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

**Samantha0101** · #1 31-03-2008, 07:47 PM

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 31-03-2008, 08:28 PM

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 01-04-2008, 07:20 PM

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 01-04-2008, 07:27 PM

You already have a database table then?

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

**Samantha0101** · #5 02-04-2008, 11:12 AM

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "<Link hidden. Register for free to see this link!>">
<html xmlns="<Link hidden. Register for free to see this link!>">
<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 02-04-2008, 11:25 AM

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 02-04-2008, 01:17 PM

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 02-04-2008, 07:36 PM

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 02-04-2008, 07:53 PM

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 <Link hidden. Register for free to see this link!> (you check that by giving <Link hidden. Register for free to see this link!> 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 02-04-2008, 08:24 PM

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.

**spot** · #11 02-04-2008, 08:41 PM

alright. And you have a valid mysql database that I can't see too.

The attachment is what I'm trying to get at. I did that with partly php and partly html and the result of the two is the same when the browser sees it because the php has executed and written the html out.

Here's the View Source which shows that there's no php left by the time your browser sees the code...

<!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>
<form id="form1" name="form1" method="post" action="EditItem.php">
<select name="itemid">
<option value=Milk>2 litre semi-skimmed Johnson Farms</option>
<option value=Eggs>2 litre eggs Johnson Farms</option><option value=Chickenbones>2 litre salmon Johnson Farms</option><option value=Milk>2 litre chickenbones Johnson Farms</option><option value=Eggs>2 litre semi-skimmed Johnson Farms</option><option value=Chickenbones>2 litre semi-skimmed Johnson Farms</option></select>
<p align='center'><input type="submit" value="Edit item"></p><p>
</form>
<form id=form2 name=form2 method=post action=EditItem.php>
<label>Item Description
<input type=text name=itemdescription value=Milk>
</label>
<p>
<label>Quantity In Stock
<input type=text name=quantityinstock value=214>
</label>
</p>
<p>
<label>Round Up Total
<input type=text name=rounduptotal value=300>
</label>
</p>
<p>
<label>Batch Price
<input type=text name=batchprice value=0.83>
</label>
</p>
<p>
<label>Supplier Name
<input type=text name=suppliername value='Johnson Farms'>
</label>
</p>
</form>
</body>
</html>

I added the missing </head> and <body>, I changed all the <? to <?php and I sort of bodged the other code because I don't have the database to work from.

Mostly you've got the right stuff there but it's not going to be easy to tell if you can't view the source you're writing, that's just flying blind in a snowstorm.

Which browser are you looking at localhost with?

**Samantha0101** · #12 03-04-2008, 12:06 PM

**spot** · #13 03-04-2008, 12:23 PM

If you read <Link hidden. Register for free to see this link!> can you get view source to work? If you can't see the html output by the php then I'm not sure how you're expected to be able to get the code correct.

I'm not sure whether you've grasped the point that the webserver reads the php but it doesn't send it on to the client browser. The client browser doesn't understand php at all. If you see any php code in your browser it means it's not been consumed by the webserver because it's not valid php, so the pre-processor ignores it and the browser ends up thinking it's just text I expect.

Tell me if there's anything I can help you with, I've lost touch with what it is you'd like to achieve in this thread. I've re-read it and I understand why I've said what I wrote but if it's not made any sense to you then I'm the wrong person to ask.

**Samantha0101** · #14 03-04-2008, 12:56 PM

Ok, thanks for attempting but i still have no idea what i'm doing.
Failing.

**spot** · #15 03-04-2008, 02:33 PM

I'l try to unravel the levels, I hope it helps a bit.

You installed Apache, php and mysql. You wrote some code in Dreamweaver and saved it as X.php and then you used Internet Explorer to browse to localhost/X.php - I think we both agree so far.

I want to show you something. Run your notepad and copy this into it:

Code:

<html><head><title>example header</title></head>
<?php
echo '<body><h1>This is your browser details</h1></body></html>';
echo '<p>You are reading this with '.$_SERVER['HTTP_USER_AGENT'].'</p>';
?> 
</body></html>

Now save that as c:\xampp\htdocs\cj.php

Browse to localhost/cj.php and see what it did. Apache has read cj.php. The .php file type tells Apache to pass the content to your php package. Your php package has run the code between the <?php and the ?> tags and so long as the code was valid it's all been taken out. The code had echo (which is nearly the same as print) which wrote some valid html into the place the code ws removed from. Then the php package has handed the output back to Apache which has sent it to your browser.

You've used php to create html code. That's all it's done. The html code that it's written has to be valid and it's no different to what you'd have written without php if you'd had no php in there. In the browser, View Source and look at the html the browser was given, it says (on my computer)

Code:

<html><head><title>example header</title></head>
<body><h1>This is your browser details</h1></body></html><p>You are reading this with Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.8.1.12) Gecko/20080201 Firefox/2.0.0.12</p> 
</body></html>

That's not what you saved to disk from notepad. That's what the php system created for you.

Now, for your database accesses you've written some php code which might or might not work. The same thing's going to happen. Apache will read your .php file and pass it to the php system. What it gets back (if you had clean syntax!) is just html and nothing else. It will have unit price as "£0.43" and description as "pepsin 125ml sachet" instead of the field names you originally coded. That's what will go to your browser. The browser will see no php code at all, with any luck.

To build this system, it's far easier to take a step at a time, to first write a html file which is valid html and looks the way this final version is going to look. You have to check that it's good html, you test it somehow. That becomes the good example of the html file you want your php code to generate for you. That's very like the code I showed you in post#11.

Then you take out all the "£0.43" and "pepsin 125ml sachet" place holders and replace them with your database field names with enough php code to get the values out of the database. That's quite close to the code you showed in post#5. You have to write the post#11 code first and test that before you can try to write the post#5 code because it's too much to handle in one leap, writing the entire page clean in one stage. When you finish changing the example code into the proper production code with all the php database open and reads in it you're just testing your php code and you have a target to aim for - making the browser see the same as you started with when you wrote your example and tested it. That's why being able to View Source matters so much, so you can compare the output html code with your target html code. I don't know another way of seeing it to know what it says.

Yes there are people in the world that can write the entire thing in one single step and manage to test it so it's clean and works. That's what the experts do. I prefer to remember the "keep it simple" lesson and take it in stages.

Read that and tell me what's not making sense.

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

Computer Juice - Forums - PHP mysql, creating a drop down list that takes fields from table in mysql