wabash

I have drop downs which are generated from fields in a database. I'd like to implement an "Search All" selection into the dropdowns which searches all fields. I'm wondering what the best way to do this is? Select Plant Color:<br /> <select name="color"> <?php //for each row we get from mysql, echo a form input $querycolor="SELECT color FROM plantcolortable ORDER BY color"; $resultcolor = mysql_query ($querycolor); while($rowcolor = mysql_fetch_array($resultcolor)){ echo "<option value=\"$rowcolor[color]\">$rowcolor[color]</option>\n"; } ?> </select> </td> <td width="400" valign="top"> Select Plant Exposure:<br /> <select name="exposure"> <?php $queryexposure="SELECT exposure FROM plantexposuretable ORDER BY exposure"; $resultexposure = mysql_query ($queryexposure); while($rowexposure = mysql_fetch_array($resultexposure)){ echo "<option value=\"$rowexposure[exposure]\">$rowexposure[exposure]</option>\n"; } ?> Below is what my query looks like: <?php $query = "SELECT actualplanttable.id, actualplanttable.common1, actualplanttable.scientific, plantcolortable.color, plantexposuretable.exposure FROM actualplanttable JOIN plantcolorlinktable ON plantcolorlinktable.plant_id = actualplanttable.id AND plantcolortable.color = '$color' JOIN plantcolortable ON plantcolorlinktable.color_id = plantcolortable.id JOIN plantexposurelinktable ON plantexposurelinktable.plant_id = actualplanttable.id JOIN plantexposuretable ON plantexposurelinktable.exposure_id = plantexposuretable.id AND plantexposuretable.exposure = '$exposure' ORDER BY common1 ASC"; $result = mysql_query($query) or die(mysql_error()); if($row = mysql_fetch_array($result)) { echo "<big>Deer Resistant plants Results that are <b>$color:</b></big><br><br> "; echo "<b>".$row['common1']."</b> (". $row['scientific'].")<br> "; echo "Light Preference: ". $row['exposure']. "<br>"; echo "A blooming color: ". $row['color']; echo "<br /><br />"; while($row = mysql_fetch_array($result)) { echo "<b>".$row['common1']."</b> (". $row['scientific'].")<br> "; echo "Light Preference: ". $row['exposure']. "<br>"; echo "A blooming color: ". $row['color']; echo "<br /><br />"; } } else { echo "No results found<br /><br />"; } echo '<a href="http://www.website">Start a new Search</a>'; } ?> Any help or guidance is greatly appreciated. Thanks! Bill...

Yes! Thanks so much for this Nightslyr. Much appreciated. Bill..

Thanks Nightslyr, Yes, I'm leaving this section blank and not getting the array. I'm hoping to leave sections blank but still be able to submit other sections of the form. Can you tell me how to check if it's null? Or point me in the right direction? I've never done that before. Continued thanks. Bill....

thanks odang! That worked for the text box submissions, though when I get a bit further down in the code to the check box submission area of my code--which is using a foreach statement, I still get errors for not inserting data: I tried using the error code you provided: <?php //insert exposure requirements foreach ($_POST['exposure'] as $value) { $queryexposure="INSERT INTO plantexposurelinktable (plant_id, exposure_id) VALUES ('$inserted_id','$value')"; mysql_query($queryexposure) or die (mysql_error().' in updating plantexposurelinktable'); } ?> The error is....... Warning: Invalid argument supplied for foreach() in ..... on line 51. Any ideas about this? Continued thanks for your help. Bill...

Hi there, I'm inserting data into a mysql database. My submissions works fine, however when a part of the form is not filled out the php scripit crashes. How can I submit all data that is set and skip pieces that aren't filled out? For example I have a plant database which has an option to insert different types of common names for the plant. It's ok if they aren't all filled out. My code is as follows: <?php //insert general plant data into db $queryplant="INSERT INTO actualplanttable (id, scientific, common1, common2, common3, family, genus, description, planting, growing, care, duration, reference) VALUES ('NULL', '$_POST[scientific]', '$_POST[common1]', '$_POST[common2]', '$_POST[common3]', '$_POST[family]', '$_POST[genus]', '$_POST[description]', '$_POST[planting]', '$_POST[growing]', '$_POST[care]','$_POST[duration]','$_POST[reference]')"; mysql_query($queryplant) or die ('Error updating actualplanttable'); //insert maintenance data into db $querymaintenance="INSERT INTO plantmaintenancelinktable (plant_id, maintenance_id) VALUES ('$inserted_id', '$_POST[maintenance]')"; mysql_query($querymaintenance) or die ('Error updating plantdurationtable'); ?> Should I be trying to work an isset function into this code? Any help would be greatly appreciated. Thanks! Bill...

Wahoo, perfect. Thanks phppaper

Hi there, I am trying to insert selections from check boxes into new rows of a mysql table. The idea is to submit a new plant to a database and include it's various flower colors. For example if a plant is submitted with three plant colors from the check box selections, three new rows in the database would be created for that plant. plantcolorlinktable plant_id color_id 1 14 1 15 2 11 2 15 2 16 My html code: <html> <head> <title>Add Plant to Database</title> </head> <body> <form method="post" action="addPlant.php"> <b><big>Plant Insert Admin tool: </b></big><br /><br /> <b> General Plant Info </b> <br /> Enter Plant's Scientific Name: <br /> <input type ="text" name="scientific" size"30" /><br /> Plant Common Name: <br /> <input type ="text" name="common" size"30" /><br /> Plant Family Name: <br /> <input type ="text" name="family" size"30" /><br /> Plant Genus Name: <br /> <input type ="text" name="genus" size"30" /><br /> <b> Plant Flowering Colors: </b> <br /> Red:<input type="checkbox" name="color[]" value="11" >:<br /> Yellow:<input type="checkbox" name="color[]" value="14">:<br /> White:<input type="checkbox" name="color[]" value="15">:<br /> <br /> <input type ="submit" value="Update Database" /> </form> </body> </html> And my php code: <?php $scientific = $_POST['scientific']; $common = $_POST['common']; $family = $_POST['family']; $genus = $_POST['genus']; $color=$_POST['color']; //????? //connect to db require_once 'login.php'; $db_server = mysql_connect($db_hostname,$db_username,$db_password); if (!$db_server) die("Unable to connect to MySQL:" . mysql_error()); mysql_select_db($db_database) or die("Unable to select database:" .mysql_error()); //insert plant data into db $queryplant="INSERT INTO actualplanttable (id, scientific, common, family, genus) VALUES ('NULL', '$_POST[scientific]', '$_POST[common]', '$_POST[family]', '$_POST[genus]')"; mysql_query($queryplant) or die ('Error updating actualplanttable'); // finding last Id inserted $inserted_id = mysql_insert_id(); //insert color data into plantcolorlinktable......I'm not sure what to do here with multiple selections from checkboxes?? $queryid="INSERT INTO plantcolorlinktable (plant_id, color_id) VALUES ('$inserted_id','$color' )"; mysql_query($queryid) or die ('Error updating the plant_id in plantcolorlinktable'); echo "Database Updated With: " .$scientific. " ".$common. " ".$family. " ".$genus. " ".$description; ?> As you can see I'm not sure what to do when it comes to inserting the check box section in my php code Any help would be greatly appreciated. Thanks! Bill....

Ah thanks guys. I was trying to do this earlier but couldn't find any examples of this scope and didn't know it was possible. Mikosiko your code worked perfectly thanks so much for writing that up for me! Thanks again!

I have two queries $queryColor and $queryMaintenance.... I'm able to successfully echo the following queries individually: $queryColor = "SELECT actualplanttable.id,actualplanttable.common, plantcolorlinktable.plant_id, plantcolorlinktable.color_id, plantcolortable.color, plantcolortable.id FROM plantcolorlinktable INNER JOIN actualplanttable ON plantcolorlinktable.plant_id = actualplanttable.id INNER JOIN plantcolortable ON plantcolorlinktable.color_id = plantcolortable.id AND plantcolortable.color = '$color'"; $resultColor = mysql_query($queryColor) or die(mysql_error()); $queryMaintenance = "SELECT actualplanttable.id, actualplanttable.common, plantmaintenancelinktable.plant_id, plantmaintenancelinktable.maintenance_id, plantmaintenancetable.id, plantmaintenancetable.maintenance FROM plantmaintenancelinktable INNER JOIN actualplanttable ON plantmaintenancelinktable.plant_id = actualplanttable.id INNER JOIN plantmaintenancetable ON plantmaintenancelinktable.maintenance_id = plantmaintenancetable.id AND plantmaintenancetable.maintenance = '$maintenance'"; $resultMaintenance = mysql_query($queryMaintenance) or die(mysql_error()); Now, I'm looking to only echo the plants that are both = to $resultColor and $resultMaintenance I.E. I now get results for several plants that are "yellow" and several plants that are "low maintenance" from these queries. I'm looking for help to only echo results of plants that are both "yellow" and "low maintenance only". Any help would be greatly appreciated! Thanks Bill...

Ah, that makes a lot of sense. Thanks guys I really appreciate it. Bill...

Thanks Keith, So would this link table look like the following?: flowerColorTable flower id, red, whtie, green yellow, purple,... 23,0,1,0,1,0 I include the flower id and put a #1 if the flower has that blooming color? Something like this or are you thinking of different? Thanks Bill...

I'm looking for the best practice to assign multiple blooming colors to a plant. For example a Yarrow plant blooms white and yellow flowers. I have a plant table and plant color table (among others) which look like: plantTable id, plant, color 1, Yarrow, ?? plantColorTable id, color 1, yellow 2, white 3, red etc. ?? Can I put multiple color Id's in a single field for color in the plantTable or is there a better practice for something like this? Thanks so much for your help. Bill....

This worked great Keith. Many thanks for taking me through this process. Cheers! Bill

Hey Guys, Just wanted to say thanks for your help, I've got the search query going w/ two drop downs. Here is the code as it stands working <?php require_once 'login.php'; $db_server = mysql_connect($db_hostname,$db_username,$db_password); if (!$db_server) die("Unable to connect to MySQL:" . mysql_error()); mysql_select_db($db_database) or die("Unable to select database:" .mysql_error()); $color = $_POST["color"]; $amount = $_POST["amount"]; if (!isset($_POST['submit'])) { echo <<<_END <html> <head> <title>Make Test</title> </head> <body> <table style="height: 70px;" border="0" width="400"> <tbody> <tr> <td width="200" valign="top"> <form method="post" action="car6.php"> Select Amount:<br /> <select name="amount"> <option value="5000">5000</option> <option value="7000">7000</option> <option value="11000">11000</option> </select> </td> <td width="400" valign="top"> Select Color:<br /> <select name="color"> <option value="red">Red</option> <option value="green">Green</option> <option value="blue">Blue</option> </select> <input type="submit" value="submit" name="submit"> </form> </td> </tr> </tbody> </table> </body> </html> _END; } else { $query = "SELECT ActualCarsTable.Price, CarMakeTable.Make, CarColorTable.Color, CarColorTable.Id FROM ActualCarsTable INNER JOIN CarMakeTable ON ActualCarsTable.Make = CarMakeTable.Id INNER JOIN CarColorTable ON ActualCarsTable.Color = CarColorTable.Id WHERE ActualCarsTable.Price = '$amount'AND CarColorTable.Color = '$color'"; $result = mysql_query($query) or die(mysql_error()); while($row = mysql_fetch_array($result)){ echo $row['Make']. " and the amount is ". $row['Price']. " and the color is ". $row['Color']; echo "<br />"; } } ?> One last question. I'm having a hard time figuring out how to tie in a "no results found" message if the query comes up empty. Any thoughts? Again thanks, this little test has really gotten me helped me over the hump. Bill...

Thanks guys for your help. I have gotten things somewhat going. What I'm trying to do now is query using selections from two drop downs to find the right car. For this test you can search by price and color. The tables I have now are: CarMakeTable Id, Make 1, Alfa Romeo 2, Audi 3, Bentley ActualCarsTable Id, Make, Price, Color 1, 1, 10000, 1 2, 1, 11000, 2 3, 1, 5000, 3 4, 2, 7000, 1 5, 2, 4000, 2 6, 2, 11000, 3 7, 3, 20000, 1 CarColorTable Id, Color 1, Red 2, Green 3, Blue I'm having luck when searching just the vehicle price with this code: $amount = $_POST["amount"]; $query = "SELECT ActualCarsTable.Price, CarMakeTable.Make ". "FROM ActualCarsTable, CarMakeTable ". "WHERE ActualCarsTable.Make = CarMakeTable.ID AND ActualCarsTable.Price = '$amount'"; $result = mysql_query($query) or die(mysql_error()); // Print out the contents of each row into a table while($row = mysql_fetch_array($result)){ echo $row['Make']. " and amount is ". $row['Price']; echo "<br />"; } But now I'm not sure how to integrate this additional query to search for cars that match the price and color criteria...this is what I have so far I'm getting a syntax error for the query: <?php require_once 'login.php'; $db_server = mysql_connect($db_hostname,$db_username,$db_password); if (!$db_server) die("Unable to connect to MySQL:" . mysql_error()); mysql_select_db($db_database) or die("Unable to select database:" .mysql_error()); if (!isset($_POST['submit'])) { echo <<<_END <html> <head> <title>Make Test</title> </head> <body> <table style="height: 70px;" border="0" width="400"> <tbody> <tr> <td width="200" valign="top"> <form method="post" action="car5.php"> Select Amount:<br /> <select name="amount"> <option value="5000">5000</option> <option value="7000">7000</option> <option value="11000">11000</option> </select> <input type="submit" value="submit" name="submit"> </form> </td> <td width="400" valign="top"> <form method="post" action="car5.php"> Select Color:<br /> <select name="color"> <option value="red">Red</option> <option value="yellow">Yellow</option> <option value="green">Green</option> </select> <input type="submit" value="submit" name="submit"> </form> </td> </tr> </tbody> </table> </body> </html> _END; } else { $amount = $_POST["amount"]; $color = $_POST["color"]; $query = "SELECT ActualCarsTable.Price, CarMakeTable.Make, CarColorTable.Color, CarColorTable.Id" . "FROM ActualCarsTable, CarMakeTable, CarColorTable" . "WHERE ActualCarsTable.Make = CarMakeTable.ID . "AND ActualCarsTable.Color = 'CarColorTable.Id'" . "AND ActualCarsTable.Price = '$amount'" . "AND CarColorTable.Color = '$color'"; $result = mysql_query($query) or die(mysql_error()); // Print out the contents of each row into a table while($row = mysql_fetch_array($result)){ echo $row['Make']. " and the amount is ". $row['Price']. " and the color is ". $row['color']; echo "<br />"; } } ?> Continued thanks, you have been so helpful thus far!

Thanks Juddster/Keith. I've removed the ' from around the the tables which fixed my syntax error. I'm now able to select from the dropdown but get the following error on submission: Fatal error: Call to undefined function interval() Here is the code as it is now: <?php require_once 'login.php'; $db_server = mysql_connect($db_hostname,$db_username,$db_password); if (!$db_server) die("Unable to connect to MySQL:" . mysql_error()); mysql_select_db($db_database) or die("Unable to select database:" .mysql_error()); $query = "SELECT * FROM CarMakeTable"; $result = mysql_query($query); if (!$result) die ("Database access failed: " .mysql_error()); $make = $_POST["make"]; if (!isset($_POST['submit'])) { // if page is not submitted to itself echo the form echo <<<_END <html> <head> <title>Make Test</title> </head> <body> <form method="post" action="car3.php"> Select a Car Make:<br /> <select name="make"> <option value="1">Alfa Romeo</option> <option value="2">Audi</option> <option value="3">Bentley</option> </select> <input type="submit" value="submit" name="submit"> </form> </body> </html> _END; } else { $sql = "SELECT a.Id, b.Make, a.Price FROM ActualCarsTable a INNER JOIN CarMakeTable b ON a.Make = b.Id WHERE b.Id = ".interval($_REQUEST['make']); } ?> Can I get some guidance on how to define the .interval function or is there an easier way to do this section? Continued thanks Bill...

Hi again, I've been trying to piece this test of mine together based on the example you've provided. I'm able to access the database tables and build the form. I'm just having a hard time figuring out how initiate the code you provided once the user makes a selection from the dropdown and presses submit This is the code I'm looking to implement: $sql = "SELECT a.Id, b.Make, a.Price FROM ActualCarsTable a INNER JOIN CarMakeTable b ON 'a.Make' = 'b.Id' WHERE 'b.Id' = ".interval($_REQUEST['make'])"; This is what I've got going so far: <?php require_once 'login.php'; $db_server = mysql_connect($db_hostname,$db_username,$db_password); if (!$db_server) die("Unable to connect to MySQL:" . mysql_error()); mysql_select_db($db_database) or die("Unable to select database:" .mysql_error()); $query = "SELECT * FROM CarMakeTable"; $result = mysql_query($query); if (!$result) die ("Database access failed: " .mysql_error()); $make = $_POST["make"]; if (!isset($_POST['submit'])) { // if page is not submitted to itself echo the form echo <<<_END <html> <head> <title>Make Test</title> </head> <body> <form method="post" action="car3.php"> Select a Car Make:<br /> <select name="make"> <option value="1">Alfa Romeo</option> <option value="2">Audi</option> <option value="3">Bentley</option> </select> <input type="submit" value="submit" name="submit"> </form> </body> </html> _END; } else { $sql = "SELECT a.Id, b.Make, a.Price FROM ActualCarsTable a INNER JOIN CarMakeTable b ON 'a.Make' = 'b.Id' WHERE 'b.Id' = ".interval($_REQUEST['make'])"; } ?> I'm getting a syntax error for this line $sql = "SELECT a.Id, b.Make, a.Price any guidance on how I tie in the $sql variable would be so helpful. Continued thanks!

Hey Keith, I can't thank you enough. This gets me going in the right direction.

Hello, I'm looking for an example or tutorial on how to populate a list based on selected items from multiple drop down menus. Similar to how a real-estate website works or how cars.com works. Using cars as an example how would I go about echoing search results from three different drop down menus? Say the drop down categories are: Make, Model and Color? A user selects one out of every category and then clicks the search button. All items that meet the specifications of the search are printed. Any help would be greatly appreciated.