Select and print with PHP & MySQL

Cups · December 9, 2010, 3:08pm

the sample code I posted was for insert.php, it mixed html with php and posted back to itself.

I just wanted to illustrate one way of debugging and “peering in” to see what is going on.

The fundamental first question is this: “Is my html correctly formed, and is PHP picking up the values?”

If so, prove it, then move on to putting it into your database.

When you finally have everything working then you remove these “sanity checks”, but you should not be doing that on a live server because you leave yourself open to hacks unless you password protect the folder.

In order to develop safely and at easily you ideally need to install a server on a local machine, in my eyes this is a fundamental part of the process of learning PHP.

CSU_Bill · December 9, 2010, 4:14pm

Cups,

So all I need to do is copy your code (form and php) and name it insert.php, and then test it?

I will be getting a development server setup soon. The entire site I am working on is password protected, and when I am ready for the site to go live, I will have a password protected folder with things such as thins safely away from prying eyes.

Cups · December 9, 2010, 4:36pm

So all I need to do is copy your code (form and php) and name it insert.php, and then test it?

Exactly.

I will be getting a development server setup soon.

Good news, that’ll get you into a much quicker and far more comfortable try, test, fail, try-again loop.

CSU_Bill · December 9, 2010, 8:39pm

Cups,

It worked. I just entered a string of characters and clicked submit. The result was:

array(4) { [“store”]=> string(10) “aaaaaaaaaa” [“saledate”]=> string(11) “bbbbbbbbbbb” [“saleitem”]=> string(12) “cccccccccccc” [“saleprice”]=> string(15) “ddddddddddddddd” }

I noticed that two of the inputs had both name and id and two had only name. Is there a reason for that?

Cups · December 9, 2010, 10:42pm

Ah, CSU-Bill, I am ashamed to say I can never remember which of the two (id or name) works when dealing with which language, JS/jQuery and the DOM and the world of strict xHTML and PHP and the backend (I could look it up, but it is one of those rare occasions I have no memorable Google search string to hand).

So in my example I tried them both out and clearly “name” is the HTML/PHP transfer attribute.

So, now you have got a working example of

a) a well formed html form (for your particular browser) and
b) PHP working and receiving the vars loud and clear

… so the next thing is to insert those vals into your database.

Next step is to look closely at the typical data you will have in your database, for this you will need to come up with some simple test data - which is easy enough to insert but also be wide-ranging enough for us to be able to model your data, imagining the various scenarios of how to slice and dice it.

This is something that is very rarely done right at the first attempt (in fact almost never).

CSU_Bill · December 10, 2010, 2:40am

Is there a text character limit in MySql? One of the samples I have is over 450 characters?

I found a way to set the width and height of the input boxes. Is there a way to provide word-wrap?

CSU_Bill · December 13, 2010, 12:57am

Cups,

I have installed XAMPP on one of my Windows machines. I tried to use phpMyAdmin to create a table and this is what I have set:

Field: id Type: INT Length/Value: 11

I left Default, Collation, Attributes, and Null all blank.

Index: UNIQUE A_I is checked

I left Comments, MIME type, Browser transformation, and Transformation options all blank.

I also created several other columns but could not figure out a way to set the primary key.

Did I get close to correct?

Cups · December 13, 2010, 2:53pm

Why don’t you explain what the data is that you are going to be storing?

Post one or two samples.

CSU_Bill · December 13, 2010, 3:21pm

I will be storing the name of the store, category, itemName, itemCost, comments, a few links.

store: Dollar General
category: Three Day Sale 12/14/2010-12/16/2010
itemName: Delmonte whole peaches
itemCost: $25.00 per flat
comments: This is the best deal this year. Best hurry before they are all gone.
links01: Delmonte Whole Peaches coupon www.delmonte.com/peaches/

Still trying to get my daughter to give me some more examples, but I made up what I have imagined to be what she wants.

Since the links could apply to more than one store, should I have a table just for coupons? Can I even access multiple tables? Can I actually store HTML in a database (links)?

Cups · December 14, 2010, 3:42pm

If you know you are never going to have more than one product called “Delmonte whole peaches” then that would be a good candidate for the primary key.

But I will guess that you could have more than one, so I would lead you down the traditional sql route of having an id in your table, making it an integer and selecting auto-increment when you create the table.

table_name_here
=============

id int auto-increment
category varchar 100
itemName varchar 100
itemCost varchar 30
comments text
links01 varchar 255

==============

Although its very tempting I am not going to stray from your first spec until we come across reasons why each database field could be improved.

Putting in an auto-increment id is a pretty good first start, read up on it and you will discover why.

Add another couple of rows.

Read up on this principle : database normalization.

Cups · December 14, 2010, 3:42pm

If you know you are never going to have more than one product called “Delmonte whole peaches” then that would be a good candidate for the primary key.

But I will guess that you could have more than one, so I would lead you down the traditional sql route of having an id in your table, making it an integer and selecting auto-increment when you create the table.

table_name_here
=============

id int auto-increment
category varchar 100
itemName varchar 100
itemCost varchar 30
comments text
links01 varchar 255

==============

Although its very tempting I am not going to stray from your first spec until we come across reasons why each database field could be improved.

Putting in an auto-increment id is a pretty good first start, read up on it and you will discover why.

Add another couple of rows (I mean a couple of other products)

Read up on this principle : database normalization.

CSU_Bill · December 14, 2010, 4:38pm

Right! This is something I learned in one of my MBA classes. I should have thought of that. Coupons go in their own table, products have their own table, etc.

I will build a new set of tables when I get home tonight.

coupon_table
==============

id int unique auto-increment  &lt;--- Does unique designate this as Primary Key
coupon_name   varchar 100
coupon_description varchar 100

================

Several tables one for each store.

store_name_table
================

id int auto-increment
category varchar 100
itemName varchar 100
itemCost varchar 30
comments text
coupon01 varchar 255    &lt;---- keyed to the coupon table using coupon name
coupon02 varchar 255    &lt;---- keyed to the coupon table using coupon name
coupon03 varchar 255    &lt;---- keyed to the coupon table using coupon name

=================

Am I on the correct path now?

CSU_Bill · December 15, 2010, 3:48am

I created a database with phpMyAdmin. When I saved the information, this was displayed:


CREATE TABLE `test`.`iga_table` (
`id` INT( 6 ) NOT NULL AUTO_INCREMENT ,
`category` VARCHAR( 100 ) NOT NULL ,
`itemname` VARCHAR( 100 ) NOT NULL ,
`itemcost` VARCHAR( 30 ) NOT NULL ,
`comments` VARCHAR( 500 ) NOT NULL ,
`coupon01` VARCHAR( 255 ) NOT NULL ,
`coupon02` VARCHAR( 255 ) NOT NULL ,
`coupon03` VARCHAR( 255 ) NOT NULL ,
`coupon04` VARCHAR( 255 ) NOT NULL ,
`coupon05` VARCHAR( 255 ) NOT NULL ,
PRIMARY KEY ( `id` ) ,
UNIQUE (
`id`
)

If this indicates that I completed it correctly, I will create the coupon database and start putting test data in the database.

Cups · December 15, 2010, 8:55am

I am still working from your original spec, that you want to just get a list of products on a single page.

It would be really easy to break off and make this a database design thread, but I understood your plea to be to help you a) get a database made (you’ve now done this) and then b) get PHP to query that database and display some products.

So, what are you using to get PHP to talk to Mysql?

PHPs native mysql_* functions
PHPs native mysqli_* functions
PDO

Take a look at the output from


phpinfo();

and see which extensions are installed for you to do this. Now, this is important, on your LIVE server which extensions are installed permitting you to access Mysql?

You want to create scripts which work flawlessly on both your dev machine and your live server.

We can easily revisit your database design once the above has been established, and proven to be working.

CSU_Bill · December 15, 2010, 2:06pm

Cups,

Thanks for pointing me back to the track. It would be so easy to loose focus and try to do it all at once.

I have just run phpinfo on my Live Server. I now have 19 pages of printout to look at.
I see that I have PHP 4.4.9 on the Live Server.
I do not find anything about PDO in the file.
I found the following mysql_* entries in the file:
MYSQL_MODULE_TYPE external
MYSQL_SOCKET /var/run/mysqld/mysqld.sock
MYSQL_INCLUDE -I/usr//include/mysql
MYSQL_LIBS -L/usr//lib -lmysqlclient

I did not find anything about mysqli_ in the file.

When I get home later tonight, I will check the phpinfo for my local installation.

axisaxis · December 15, 2010, 5:15pm

Cups:

Thinking about my reply, just try this first if you like:

insert.php


<form action="insert.php" method="post">
Store name: <input type="text" name="store" id="store"><br>
Sale Dates: <input type="text" name="saledate" id="saledate"><br>
Sale Item: <input type="text" name="saleitem"><br>
Sale Price: <input type="text" name="saleprice"><br>
<input type="Submit">
</form>
<?php  // note, use full php tags

if( $_POST ){
var_dump($_POST) ;
}
?>

Save it and run it, fill in the form, get any output?

made it…

Cups · December 15, 2010, 5:28pm

OK, I cannot see it so I will guess that server is using mysql_* functions (mysql_connect() etc).

If your dev server has the same setup then you should be able to connect to mysql using code you will find in most of the PHP/Database tutorials on the web - or start in the manual

Still, you need to get a test script working which proves this case.
test_mysql.php


<?php

       if(!is_callable("mysql_connect")){
        echo "MySQL module for PHP not installed or loaded, please see php.ini" ;
        return;
      }else{
       echo "Mysql module is installed" ;
}

print_r(get_loaded_extensions());

?>

[fphp]get_loaded_extensions[/fphp]

Put that script on both servers and note any diffs when it comes to databases, or as I say, mysqli, PDO and maybe even sqlite.

Cups · December 15, 2010, 5:30pm

Er? sorry mate, right person, wrong thread? Good on you in any case.

CSU_Bill · December 15, 2010, 5:37pm

I just ran that on the live server, and this is what I got:

Mysql module is installedArray ( [0] => date [1] => libxml [2] => openssl [3] => pcre [4] => zlib [5] => bcmath [6] => bz2 [7] => calendar [8] => ctype [9] => curl [10] => dba [11] => dbase [12] => dom [13] => hash [14] => filter [15] => ftp [16] => gd [17] => gettext [18] => session [19] => iconv [20] => idn [21] => standard [22] => json [23] => mbstring [24] => mcrypt [25] => mhash [26] => mysql [27] => SimpleXML [28] => SPL [29] => PDO [30] => pdo_sqlite [31] => posix [32] => Reflection [33] => imap [34] => shmop [35] => pdo_mysql [36] => soap [37] => mysqli [38] => SQLite [39] => exif [40] => tidy [41] => tokenizer [42] => wddx [43] => xml [44] => xmlreader [45] => xmlwriter [46] => xsl [47] => zip [48] => cgi )

Now I am off to read the link in your message. Sometime tonight, I will run this on my home server setup.

Cups · December 15, 2010, 6:37pm

OK, so that shows your live server has lots of options for linking to Mysql, PDO drivers for sqlite and mysql, and native mysql_* functions too.

Stick to the latter as you find your feet, so now to see what your home rig has got.