phpstudent111

Hi Everyone, I have a resultset from mysql displayed on my front-end in table format. I have added two extra columns for an "Edit" and "Delete" button but am having trouble writing the functionality. Mainly, I need to store each cell from the resultset in it's own variable, so I can then pre-populate the "Edit" function with the appropriate variables, etc. Please let me know if that's not clear enough and please find my code below! Thanks for all of your help! $mymodel=$_POST['mymodel']; $mymanufacturer=$_POST['mymanufacturer']; $myyear=$_POST['myyear']; $mydoors=$_POST['mydoors']; $mypricemin=$_POST['mypricemin']; $mypricemax=$_POST['mypricemax']; $mycolor=$_POST['mycolor']; $myinteriorcolor=$_POST['myinteriorcolor']; $query='SELECT car.id , model.manufacturer , model.modelname , car.year , color.color_name , interior_color.interior_color , car.doors , car.retailprice , car.dealercost , transmission_type.transmission_type FROM car INNER JOIN model ON (car.model_id=model.id) INNER JOIN color ON (car.color_id=color.id) INNER JOIN interior_color ON (interior_color.id=car.interior_color_id) INNER JOIN transmission_type ON (car.transmission_type_id=transmission_type.id)'; //HERE $where = array(); if(!empty($_POST['mymodel'])) $where[] = sprintf("car.model_id='%s'",mysql_real_escape_string($_POST['mymodel'])); if(!empty($_POST['mymanufacturer'])) $where[] = sprintf("model.manufacturer_id='%s'",mysql_real_escape_string($_POST['mymanufacturer'])); if(!empty($_POST['myyear'])) $where[] = sprintf("car.year='%s'",mysql_real_escape_string($_POST['myyear'])); if(!empty($_POST['mydoors'])) $where[] = sprintf("car.doors='%s'",mysql_real_escape_string($_POST['mydoors'])); if(!empty($_POST['mypricemin'])) $where[] = sprintf("car.retailprice>='%d'",mysql_real_escape_string($_POST['mypricemin'])); if(!empty($_POST['mypricemax'])) $where[] = sprintf("car.retailprice<='%d'",mysql_real_escape_string($_POST['mypricemax'])); if(!empty($_POST['mycolor'])) $where[] = sprintf("car.color_id='%s'",mysql_real_escape_string($_POST['mycolor'])); if(!empty($_POST['myinteriorcolor'])) $where[] = sprintf("car.interior_color_id='%s'",mysql_real_escape_string($_POST['myinteriorcolor'])); if(count($where)) $query .= ' WHERE '.implode(' AND ',$where); $result=mysql_query($query) or die (mysql_error()); $fields_num=mysql_num_fields($result); echo "<p align='center'>Results:</p><table width='75%' border='1' align='center'><tr>"; //printing talbe headers for($i=0; $i<$fields_num; $i++) { $field=mysql_fetch_field($result); echo "<td>{$field->name}</td>"; } echo "</tr>\n"; //print table rows while($row=mysql_fetch_row($result)) { echo "<tr>"; foreach($row as $cell) echo "<td>$cell</td>"; echo "<td><form method='LINK' action='edit.php'><input type='submit'value='Edit'></form></td><td><form method='LINK' action='delete.php'><input type='submit'value='Delete'></form></td>\n"; echo "</tr>\n"; } mysql_free_result($result); ob_end_flush(); ?>

Wow. Thanks again, it works perfectly.

Something like - "40000.00" - But I can easily change them to dropdown values incrementing by 10,000 - if we are able to select cars from within the 2 price ranges. I would make them Retail Cost within: and 2 dropdowns to select from....This is just a learning project for me. It's never going to be used..thanks again!

I'd really like to have them populate directly from the database, but this is my first application i'm building so I kept it fairly simple. Thanks again for getting that search to work. I really appreciate it. <h2 align="center"> Inventory Management System</h2> <table id="inventory align="center"> <form name="search" method="post" action="search.php"> <br><br><p><strong>Please Search Below:</strong></p> <tr><td>Manufacturer:</td> <td><select name="mymanufacturer" size="1"> <option value=0>Manufacturer</option> <option value="200">Acura</option> <option value="201">Infiniti</option> <option value="202">Porsche</option> <option value="203">Maserati</option> <option value="204">Ford</option> <option value="205">GM</option> <option value="206">Dodge</option> <option value="207">Chrysler</option> <option value="208">Mercedes</option> <option value="209">Chevrolet</option> <option value="210">Honda</option> <option value="211">Toyota</option> <option value="212">Subaru</option> <option value="213">Hyundai</option> <option value="214">Saturn</option> <option value="215">Land Rover</option> <option value="216">Lotus</option> <option value="217">Nissan</option> <option value="218">Lincoln</option> </td></tr> <tr><td>Model:</td> <td><select name="mymodel" size="1"> <option value=0>Model</option> <option value="1">TL</option> <option value="2">Integra</option> <option value="3">TSX</option> <option value="4">RSX</option> <option value="5">Altima</option> <option value="6">Sentra</option> <option value="7">Maxima</option> <option value="8">S230</option> <option value="9">C330</option> <option value="10">Benz</option> <option value="11">Quatroporte</option> <option value="12">Boxster</option> <option value="13">Carrera</option> <option value="14">Mustang</option> <option value="15">Taurus</option> <option value="16">Envoy</option> <option value="17">Towncar</option> <option value="18">Sky</option> <option value="19">Ram</option> <option value="20">Challenger</option> <option value="21">G35</option> <option value="22">Corvette</option> <option value="23">F150</option> <option value="24">Accord</option> </td></tr><tr> <td> <tr><td>Doors:</td> <td><select name="mydoors" size="1"> <option value=0>Doors</option> <option>2</option> <option>4</option> <option>5</option></td></tr> <tr><td> Dealer Cost: </td><td> <input name="mydealercost" type="text" name="mydealercost" /></br></td> </tr><tr><td> Retail Price:</td><td> <input name="myretailprice" type="text" id="myretailprice" /></td></br> </tr> <td> <tr><td>Year:</td> <td><select name="myyear" size="1"> <option value=0>Year</option> <option>1980</option> <option>1981</option> <option>1982</option> <option>1983</option> <option>1984</option><option>1985</option><option>1986</option><option>1987</option> <option>1988</option><option>1999</option><option>2000</option><option>2001</option> <option>2002</option><option>2003</option><option>2004</option><option>2005</option> <option>2006</option><option>2007</option><option>2008</option><option>2009</option><option>2010</option> </td></tr><tr> <td> <tr><td>Color:</td> <td><select name="mycolor" size="1"> <option value=0>Color</option> <option value="1">Black</option> <option value="2">White</option> <option value="3">Red</option> <option value="4">Orange</option> <option value="5">Yellow</option> <option value="6">Violet</option> <option value="7">Blue</option> <option value="8">Green</option> <option value="9">Silver</option> </td></tr> <tr><td>Interior Color:</td> <td><select name="myinteriorcolor" size="1"> <option value=0>Interior Color</option> <option value="1">Black Leather</option> <option value="2">White Leather</option> <option value="3">Blue Leather</option> <option value="4">Red Leather</option> <option value="5">Silver Leather</option> <option value="6">Black Cloth</option> <option value="7">White Cloth</option> <option value="8">Blue Cloth</option> <option value="9">Red Cloth</option> <option value="10">Silver Cloth</option> </td></tr> <tr><td><br><br> <input type="submit" name="submit" value="Search" /></td></tr></br> </form> </div> </table> </body> </html>

It works perfectly. I just had to modify some of the calls to the database, ie car.model...which you couldn't have possibly known. You are a genius, you are the man. Thank you so much I really appreciate it. If you want to get really crazy...You can help me to modify my WHERE clause to make the dealercost and retailprice search between the two values inserted.

Thanks. I have drop down menu's for the HTML form, passing the ID's that need to be either included or omitted from the WHERE clause in the mysql based on whether or not they are empty, null, or zero. I can pass whichever values work. i've tried NULL and empty and return no results with either... Any ideas?

Thanks so much. I can tell that is so close to working 2 questions. 1.. In my results now on the front end, it prints SELECT car.id , model.manufacturer , model.modelname , car.year , color.color_name , interior_color.interior_color , car.doors , car.retailprice , car.dealercost , transmission_type.transmission_type FROM car INNER JOIN model ON (car.model_id=model.id) INNER JOIN color ON (car.color_id=color.id) INNER JOIN interior_color ON (interior_color.id=car.interior_color_id) INNER JOIN transmission_type ON (car.transmission_type_id=transmission_type.id) WHERE model.id='NULL' AND model.manufacturer='200' AND car.year='NULL' AND car.doors='NULL' AND color.color_name='NULL' AND interior_color.interior_color='NULL' and then my table...so maybe an extra quote? and 2..with the if(!empty....etc -- do I pass a "NULL" for null variables or a "0" ? Thanks again so much.

I cut and pasted your code. I'm not sure what you mean by closing the string. The whole thing now looks like this... mysql_connect("$host", "$username", "$password", 1, 131072)or die("Unable to Connect"); mysql_select_db("$db_name")or die("Cannot Select Database"); //defining variables $mymodel=$_POST['mymodel']; $mymanufacturer=$_POST['mymanufacturer']; $myyear=$_POST['myyear']; $mydoors=$_POST['mydoors']; $mydealercost=$_POST['mydealercost']; $myretailprice=$_POST['myretailprice']; $mycolor=$_POST['mycolor']; $myinteriorcolor=$_POST['myinteriorcolor']; $query="SELECT car.id , model.manufacturer , model.modelname , car.year , color.color_name , interior_color.interior_color , car.doors , car.retailprice , car.dealercost , transmission_type.transmission_type FROM car INNER JOIN model ON (car.model_id=model.id) INNER JOIN color ON (car.color_id=color.id) INNER JOIN interior_color ON (interior_color.id=car.interior_color_id) INNER JOIN transmission_type ON (car.transmission_type_id=transmission_type.id) $where = array(); if(!empty($_POST['mymodel'])) $where[] = sprintf("model.id='%s'",mysql_real_escape_string($_POST['mymodel'])); if(!empty($_POST['mymanufacturer'])) $where[] = sprintf("model.manufacturer='%s'",mysql_real_escape_string($_POST['mymanufacturer'])); if(!empty($_POST['myyear'])) $where[] = sprintf("car.year='%s'",mysql_real_escape_string($_POST['myyear'])); if(!empty($_POST['mydoors'])) $where[] = sprintf("car.doors='%s'",mysql_real_escape_string($_POST['mydoors'])); if(!empty($_POST['mydealercost'])) $where[] = sprintf("car.dealercost='%s'",mysql_real_escape_string($_POST['mydealercost'])); if(!empty($_POST['myretailprice'])) $where[] = sprintf("car.retailprice='%s'",mysql_real_escape_string($_POST['myretailprice'])); if(!empty($_POST['mycolor'])) $where[] = sprintf("color.color_name='%s'",mysql_real_escape_string($_POST['mycolor'])); if(!empty($_POST['myinteriorcolor'])) $where[] = sprintf("interior_color.interior_color='%s'",mysql_real_escape_string($_POST['myinteriorcolor'])); if(count($where)) $query .= ' WHERE '.implode(' AND ',$where); print $query; $result=mysql_query($query) or die (mysql_error()); $fields_num=mysql_num_fields($result); echo "<table border='1'><tr>"; //printing talbe headers for($i=0; $i<$fields_num; $i++) { $field=mysql_fetch_field($result); echo "<td>{$field->name}</td>"; } echo "</tr>\n"; //print table rows while($row=mysql_fetch_row($result)) { echo "<tr>"; foreach($row as $cell) echo "<td>$cell</td>"; echo "</tr>\n"; } mysql_free_result($result); ob_end_flush(); ?> </body> </html>

Thank you for your quick response. i added your code, I got the following error message Parse error: syntax error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING in /home/content/j/w/a/jwalkow111/html/search.php on line 50 The WHERE clause was omitted from the sql query in favor of the variable $where...could that be why? i'm still a newbie and my system will be complete if I can only get this code to work...thanks again for your help it is much much appreciated

Hi, Any help would be greatly appreciated as I've been beating my head against the wall with this one. I have 7 variables and I am trying to populate the WHERE clause of a mysql_query based on whether or not these variables are NULL. Is this possible? Here is the code I have... $mymodel=$_POST['mymodel']; $mymanufacturer=$_POST['mymanufacturer']; $myyear=$_POST['myyear']; $mydoors=$_POST['mydoors']; $mydealercost=$_POST['mydealercost']; $myretailprice=$_POST['myretailprice']; $mycolor=$_POST['mycolor']; $myinteriorcolor=$_POST['myinteriorcolor']; $query="SELECT car.id , model.manufacturer , model.modelname , car.year , color.color_name , interior_color.interior_color , car.doors , car.retailprice , car.dealercost , transmission_type.transmission_type FROM car INNER JOIN model ON (car.model_id=model.id) INNER JOIN color ON (car.color_id=color.id) INNER JOIN interior_color ON (interior_color.id=car.interior_color_id) INNER JOIN transmission_type ON (car.transmission_type_id=transmission_type.id) WHERE =???; Just something like . (in english) if $mymodelIS NOT NULL then $where="$mymodel=model.id" and then repeat that for each variable until the WHERE clause has only the "$mymodel=model.id AND $mymanufacturer=model.manufacturer_id AND $myyear=car.year", etc. But Only for the variables that are not null I hope this makes sense, please help, if you need more information please just ask.

Thanks so much. I'm just not sure how your variables relate to mine. Your code: f(!is_null($var1) { if($where=='') { $where=' WHERE field = '.$var1; } else { $where=' AND field = '.$var1; } } As I understand it - I need the WHERE clause to update with each non-null variable - basically if '$mymodel' is not null then I need "$mymodel=model.id" placed in the WHERE clause....then if $mymanufacturer is not null, I need it updated to "$mymodel=model.id AND $mymanufacturer=model.manufacturer_id" - and so on for each clause...can you please explain a little bit more

Thanks so much, I will definitely try it...I am fairly new to PHP so I have a few questions. Below is my code for your review...can I stick your code right in the WHERE clause or does it need to be before/after? Is there any way you can re-write with these variables to make it a bit more clear? Thanks so much for your help. $mymodel=$_POST['mymodel']; $mymanufacturer=$_POST['mymanufacturer']; $myyear=$_POST['myyear']; $mydoors=$_POST['mydoors']; $mydealercost=$_POST['mydealercost']; $myretailprice=$_POST['myretailprice']; $mycolor=$_POST['mycolor']; $myinteriorcolor=$_POST['myinteriorcolor']; $query="SELECT car.id , model.manufacturer , model.modelname , car.year , color.color_name , interior_color.interior_color , car.doors , car.retailprice , car.dealercost , transmission_type.transmission_type FROM car INNER JOIN model ON (car.model_id=model.id) INNER JOIN color ON (car.color_id=color.id) INNER JOIN interior_color ON (interior_color.id=car.interior_color_id) INNER JOIN transmission_type ON (car.transmission_type_id=transmission_type.id) WHERE xxxxxx; $result=mysql_query($query) or die (mysql_error()); $fields_num=mysql_num_fields($result); echo "<table border='1'><tr>"; //printing talbe headers for($i=0; $i<$fields_num; $i++) { $field=mysql_fetch_field($result); echo "<td>{$field->name}</td>"; } echo "</tr>\n"; //print table rows while($row=mysql_fetch_row($result)) { echo "<tr>"; foreach($row as $cell) echo "<td>$cell</td>"; echo "</tr>\n"; } mysql_free_result($result); ob_end_flush(); ?>

Hi, Any help would be greatly appreciated as I've been beating my head against the wall with this one. I have 7 variables and I am trying to populate the WHERE clause of a mysql_query based on whether or not these variables are NULL. Is this possible? Just something like . (in english) if $myvariable IS NOT NULL then $newvariable="$myvariable=table1.column" and then repeat that for each variable until the WHERE clause has only the "$myvariable=table1.column" portion for the variables that are not null. I hope this makes sense, please help, if you need more information please just ask. Thank you!!

Just to be more clear...the existing code in the WHERE clause (WHERE $mymodel=model.id" needs to be replaced....any ideas??

I am trying to search with a SELECT statement (mySQL) and have 8 variables I would like to include in the WHERE clause of my statement. (ie. $mymodel=model.id AND $mycolor=color.id, etc.) I'd like the variable to not be included in the WHERE statement if they are null but I am really not sure how to do this. Please let me know if you have any ideas...below is the code I have now....Thanks! <?ob_start(); ?> <!doctype html public"-//W3C//DTD HTML 4.0 //EN"> <html> <title>A/J Software Solutions-Search Results</title> <body bgcolor="#CCCCCC"> <div id="menu" align="center"> <table border="3" bordercolor="000066" valign="top" align="center"> <td> <a href="login_success.php">Search Inventory<a /> </td> <td><a href="insert.php">Add Inventory<a /> </td> <td><a href="edit.php">Edit Inventory<a/></td> <td><a href="delete.php">Delete Inventory<a /> </td> </div> <h1 align="center"> A/J Software Solutions</h1> <h2 align="center"> Inventory Management System</h2> <table id="delete" align="center"> <br><br><br> <tr><strong>Results:</strong></tr> <br><br><br> <?php ob_start(); $host="97.74.31.200"; $username="jwalkow1"; $password="Starpoint10"; $db_name="jwalkow1"; //connect to server mysql_connect("$host", "$username", "$password", 1, 131072)or die("Unable to Connect"); mysql_select_db("$db_name")or die("Cannot Select Database"); //defining variables $mymodel=$_POST['mymodel']; $mymanufacturer=$_POST['mymanufacturer']; $myyear=$_POST['myyear']; $mydoors=$_POST['mydoors']; $mydealercost=$_POST['mydealercost']; $myretailprice=$_POST['myretailprice']; $mycolor=$_POST['mycolor']; $myinteriorcolor=$_POST['myinteriorcolor']; $query="SELECT car.id , model.manufacturer , model.modelname , car.year , color.color_name , interior_color.interior_color , car.doors , car.retailprice , car.dealercost , transmission_type.transmission_type FROM car INNER JOIN model ON (car.model_id=model.id) INNER JOIN color ON (car.color_id=color.id) INNER JOIN interior_color ON (interior_color.id=car.interior_color_id) INNER JOIN transmission_type ON (car.transmission_type_id=transmission_type.id) WHERE $mymodel=model.id"; $result=mysql_query($query) or die (mysql_error()); $fields_num=mysql_num_fields($result); echo "<table border='1'><tr>"; //printing talbe headers for($i=0; $i<$fields_num; $i++) { $field=mysql_fetch_field($result); echo "<td>{$field->name}</td>"; }