PHP mysql, creating a drop down list that takes fields from table in mysql

**Samantha0101** · #1 31st Mar 2008, 12:47

How do i do this for:
a) selecting from a list of items
b) selecting two fields to edit an order (for example)
Could someone also explain the code to me please.
Appreciated.

**spot** · #2 31st Mar 2008, 13:28

This is pitched at an overview level, I'm not sure whether it's what you need or not, you've not said what experience you have with html or php. Whether it's what you want or not it provides a hook we can expand on.

You're using php in a webserver. The webserver is pre-processing your php code to let your code build html sections to send to the client as part of a complete html document. Is that true so far?

So, what you do is you decide what the html is going to say. Write a dummy html document that looks exactly the way you want your final document to look. It will have a pull-down list in it among other things and you'll have written dummy items into it to make it look right.

Then you completely test your html so it's perfect. It doesn't come from a database, it just looks good but you have to do that much. The pull-down selection code ought to work already at this stage and it has nothing to do with php.

Then you take the pull-down list and turn it into php, possibly using echo to write the html. You still have sample data but you're starting to pre-process it. Run it and check the html you get out is identical to the previous step.

You have your mysql database. Write some php to open it and (if you want to) to close it. Check there's no change and the html's not affected.

Finally, change the hard-coded bits of echo'd html into field values from a SQL read of the database. That's the small twist which lets your server provide flexible data from the database instead of hard code, it's just that little change which provides your functionality.

Which part are you stuck on?

**Samantha0101** · #3 1st Apr 2008, 12:20

I know pretty much nothing about php.
I need code that will link to my table and enable me to select certain fields?

**spot** · #4 1st Apr 2008, 12:27

You already have a database table then?

Do you have some html to add the php to yet?

**Samantha0101** · #5 2nd Apr 2008, 04:12

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Edit Item</title>
<?
$username="root";
$password="";
$database="cafe";
$dbLink=mysql_connect(localhost,$username,$passwor d);
$QueryPointer=mysql_query("USE $database",$dbLink);
if(isset($_REQUEST[itemdescription]))
{
$sql="update items set itemdescription='".$_REQUEST[itemdescription]."',";
$sql=$sql."quantityinstock=".$_REQUEST[quantityinstock].",";
$sql=$sql."rounduptotal=".$_REQUEST[rounduptotal].",";
$sql=$sql."batchprice=".$_REQUEST[batchprice].",";
$sql=$sql."suppliername='".$_REQUEST[suppliername]."'";
$sql=$sql."where itemid=".$_REQUEST[itemid];
mysql_query($sql,$dbLink);
mysql_close($dbLink);
}
?>
<form id="form1" name="form1" method="post" action="EditItem.php">
<select name="itemid">
<?
$sql="select itemid, itemdescription, suppliername from items order by itemdescription";
$QueryPointer=mysql_query($sql,$dbLink);
//print("<option>".mysql_num_rows($QueryPointer)."</option>");
for($x=0; $x<mysql_num_rows($QueryPointer); $x++)
{
$item=mysql_fetch_assoc($QueryPointer);
if($item["itemid"]==$_REQUEST[itemid])
{
print("<option value=".$item["itemid"]." selected>".$item["itemdescription"]." ".$item["suppliername"]."</option>");
}
else
{
print("<option value=".$item["itemid"].">".$item["itemdescription"]." ".$item["suppliername"]."</option>");
}
}
?>
</select>
<p align='center'><input type="submit" value="Edit item"></p><p>
</form>
<?
if(isset($_REQUEST[itemid]))
{
$sql="select itemid, itemdescription, suppliername from item where itemid=".$_REQUEST[itemid];
$QueryPointer=mysql_query($sql,$dbLink);
$patient=mysql_fetch_assoc($QueryPointer);
print("<form id=\"form2\" name=\"form2\" method=\"post\" action=\"EditItem.php\">
<label>Item Description
<input type=\"text\" name=\"itemdescription\" value=\"$item[itemdescription]\"/>
</label>
<p>
<label>Quantity In Stock
<input type=\"text\" name=\"quantityinstock\" value=\"$item[quantityinstock]\"/>
</label>
</p>
<p>
<label>Round Up Total
<input type=\"text\" name=\"rounduptotal\" value=\"$item[rounduptotal]\" />
</label>
</p>
<p>
<label>Batch Price
<input type=\"text\" name=\"batchprice\" value=\"$item[batchprice]\" />
</label>
</p>
<p>
<label>Supplier Name
<input type=\"text\" name=\"suppliername\" value=\"$item[suppliername]\" />
</label>
</p>
}
?>
</form>
</body>
</html>

Thats my code at the minute, i need the itemid and itemdescription to appear as a drop down list, to choose a record from my items table to edit.
Also, when a user inputs a value into a field (in a new items table for example) but they enter it in the wrong form how would i create error messages? 'Cause they'd just not work and the user wouldn't know they hadn't been entered into the table, right?

**Samantha0101** · #6 2nd Apr 2008, 04:25

Also, when my user wants to place an order i'd like them to be able to input a quantity and the form to bring up the total price by multiplying the batchprice (items table) by the quantity ordered..
In MS Access the SQL would be something like:

SELECT items.ItemID, items.BatchPrice, orderitem.QuantityOrdered, [BatchPrice]*[QuantityOrdered] AS TotalPrice
FROM ordersplaced, items INNER JOIN orderitem ON items.ItemID=orderitem.ItemID;

but how do i do this in php/my sql??

**spot** · #7 2nd Apr 2008, 06:17

I think you can't see the html wood for the php trees at the moment, which is why I started with that overview. If you sit as the client at the browser and View Source on that page, just to see the generated html, there's a lot missing. There's no /head, body, it's skeletal and getting it fixed up after you've got the php working is a lot harder than getting it right before you code any php.

Your drop down list has to be a working html drop down list as far as the client's concerned, tthat's what his browser will be given, his browser isn't going to see any itemid php or itemdescription php it's just going to see the value in an html setting. You're holding six juggling balls in your hand and trying to throw them all in the air at once instead of first, second, third, fourth.

There's two ways of data validating and you can either code just one of them or you can code both.

The two stage way is to javascript validate the syntax of each field on the client machine and refuse to submit the form to the server until it's clean. That's the sort of "oh, address-1 has to be between 3 and 24 characters long and it's mandatory, please try again" screen vetting. The second is the form on page-1 calls page-X which validates anything at all - syntax or database lookups. It might say "I don't have that zip code on my zipcode file" for example. If it finds an error it sends back page-1 for correction, if all the fields on the POST were clean then it forwards the whole POST to page-2 for the next stage in the process. Once page-X has validated all the fields then either page-X or page-2 can do any updating of the database that page-1 was calling for. My own opinion is that since you mostly have to have database vetting of some fields you might as well just have one validation mechanism and not bother with javascript at all. The reason for using javascript as well is to reduce the load on the server. If most page-1 screens get rejected first time round then yes, javascript can do that. It's still a further investment in time and effort when writing the system.

What I'd do, if I were you, is get a screen of html working which includes this drop down mechanism you want. No php at all, just a dummy working screen that passes the w3 html validator. You haven't got that and it won't be easy to get one when you have the php in the way as well.

Then, with a known working drop down system in place, you can change the values to ones from your database - that bit is a single line regardless of how complicated it ends up looking. It's a one-line change to a working system so it's simple to test and to know you've got working properly.

Do you have a deadline for this? Is it a college project - it seems a bit ambitious if it is and if you really do want to go as far as adding a data validation mechanism.

Are you testing on your own computer or do you have a server somewhere? If you have one, is it on the Internet or is it on your LAN?

**Samantha0101** · #8 2nd Apr 2008, 12:36

Yes it's college coursework.
What you've explained seems really complex to me and nothing like what we've been 'taught' to do (teacher sucks). Deadline is next Tuesday, i would just keep asking him but all he says is that where i'm at is 'worrying' and he takes forever to sort out 1 problem when i have TONS.
If only we could use MS Access.

**spot** · #9 2nd Apr 2008, 12:53

How are you testing what you write? Did you put a webserver on your own computer, or are you putting your code on a different computer to browse?

Either way, when you browse to the page that has the code you've shown me, View Source.

Copy that to this thread and we'll see that it has no php in it, and nowhere near as many lines.

The php has been used up in the server's pre-processor before the webserver sent you the page. The php has created lines of html.

Now, so far you haven't got a drop-down table. If you write one in just html then you know you'll be able to see it in the browser. You'll be able to test it and check that it really drops down and that it looks good. I think you need to do that first. When you've done it, browse the page off the webserver and View Source and copy/paste it here again so we see the difference. See if you can get the View Source version to be clean html that passes the validator at http://validator.w3.org/ (you check that by giving http://validator.w3.org/ the web address of your page if you have a web address for it, or pasting that View Source code into the validator directly).

If we get clean html then you get another mark on the coursework I expect. Then we can make the drop-down table come from php instead of html, from the database, and you'll get more marks still assuming the teacher can follow what you've done.

Keep talking, I expect we'll manage to find the right words eventually.

**Samantha0101** · #10 2nd Apr 2008, 13:24

Using Dreamweaver 8, xampplite and localhost to view pages, can't view source 'cause theres an error with the code and no idea on HTML.