dragon2309 Posted April 3, 2006 Share Posted April 3, 2006 hey hey everyone, how you all doing. First post here so go easy please. Heres the problem.[!--sizeo:2--][span style=\"font-size:10pt;line-height:100%\"][!--/sizeo--][b][u]Background Info:[/u][/b][!--sizec--][/span][!--/sizec--]I have been progresing through this project to get user customisable queries, as in i want the user to be able to specify a price etc... and all records that muatch get returned. Simple.... yeh.... well i got that far, and it worked. [!--sizeo:2--][span style=\"font-size:10pt;line-height:100%\"][!--/sizeo--][b][u]The Problem:[/b][/u][!--sizec--][/span][!--/sizec--]I want to call records from about 12 different tables. if i do this in the usual way "SELECT * FROM table1, table2, table3" and so on, i get an error saying &price was ambiguous. $price being the variable the user selects to filter the records by.Heres the code ive got, i dont know why it is coming up with this error....[code]<html><head><meta http-equiv="Content-Type" content="text/html; charset=windows-1252"><title>price Query</title></head><body></head> <body> <h1>price Query</h1> <form id="make_select" action="<?php $PHP_SELF; ?>" method="post"> <p> Select price: <select name="price"> <option value="£1.99">£1.99</option> <option value="£2.99">£2.99</option> <option value="£3.99">£3.99</option> </select> <input type="submit" /> </p> </form> <br> <?php if( ! empty( $_POST["price"] ) ) { echo '<table border="1" bgcolor="#FFD9FF" bordercolor="#FF9DFF" bordercolorlight="#FF9DFF" bordercolordark="#FF9DFF" cellpadding="6" style="border-collapse: collapse; font-family: Tahoma; font-size: 11pt"> <tr> <th><font face="Tahoma">Item Number</font></th> <th><font face="Tahoma">Description</font></th> <th><font face="Tahoma">Price</font></th> <th><font face="Tahoma">Image</font></th> <th><font face="Tahoma">Information</font></th> <th bgcolor="#FFECFF"> </th> <th>Item Number</th> <th>Description</th> <th>Price</th> <th>Image</th> <th>Information</th> </tr> </div> <tr> <td bgcolor="#FFECFF"> </td> <th bgcolor="#FFECFF"> </th> <th bgcolor="#FFECFF"> </th> <th bgcolor="#FFECFF"> </th> <th bgcolor="#FFECFF"> </th> <th bgcolor="#FFECFF"> </th> <th bgcolor="#FFECFF"> </th> <th bgcolor="#FFECFF"> </th> <th bgcolor="#FFECFF"> </th> <th bgcolor="#FFECFF"> </th> <th bgcolor="#FFECFF"> </th> </tr>'; require "dbinfo.php"; // Connect to MySQL DBMS if (!($connection = @ mysql_connect(DB_HOSTNAME, DB_USERNAME, DB_PASSWORD))) showerror(); // Use the cars database if (!mysql_select_db(DB_DATABASENAME, $connection)) showerror(); // Create SQL statement $price = $_POST["price"]; $query = "SELECT * FROM ad_brac, ch_brac, ad_neck WHERE Price='$price' AND StockCount > 0"; // Execute SQL statement if (!($result = @ mysql_query ($query, $connection))) showerror(); // starts counter at 0 $counter=-1; while ($row = @ mysql_fetch_array($result)) { if($counter == 2){//this will tell us we already have 2 items in the row so start a new row echo "</tr>";//close the old row $counter=0; //reset the counter as we will now be starting a new row echo "<tr>";//start a new row } if ($counter == -1) { //this code will only action on the first time through, after that counter will hold values on 0,1,or 2 $counter=0; //reset the counter to begin echo "<tr>";//start the first row of the results } if($counter < 2){ //if we already have 2 items in the row then we need to start a new row echo "<td>{$row['StockCode']}</td> <td>{$row['Description']}</td> <td>{$row['Price']}</td> <td><p><a href='{$row['IMGURL']}'><img src='{$row['IMG']}'></img></p></td> <td><center>{$row['URL']}</center></td>"; if ($counter == 0) echo '<td bgcolor="#FFECFF"> </td>'; //do this to ensure a nice space between the two coloumn $counter++; //increment the counter to show we have added one item to the current row } }//end of while loop //so now we are out of the while loop, we need to assess how the table ended. how many items are in the last row //if there is only one then we need to flesh out the second item in that row is to make it look good if ($counter==1){ echo " <td> </td> <td> </td> <td> </td> <td> </td> <td> </td>"; } echo "</td></table>"; } if($counter == -1) echo "<br><br><font face='Tahoma' style='font-size: 11pt'>We're sorry, simplyTrue currently do not have any items of jewellery in stock that meet your criteria. Please try your search again or use the <a href='http://www.simplytrue.co.uk/customnotes.htm'><b>Custom Jewellery</b></a> link to request a specific item of jewellery.<br><br>Thankyou, simplyTrue.</font>"; ?></body></html>[/code]The page is located at [a href=\"http://www.simplytrue.co.uk/stocklist/query_new.php\" target=\"_blank\"]http://www.simplytrue.co.uk/stocklist/query_new.php[/a]Please help me, i am at a loss as to what to do...Thankyou, [b]dragon2309[/b] Quote Link to comment Share on other sites More sharing options...
Barand Posted April 4, 2006 Share Posted April 4, 2006 SELECT * FROM table1, table2, table3....Queries like this, with no join condition, will join all records from the tables will all records from the other tables, so if you have 100 records in each table you end up querying 100 x 100 x 100 records (ie 1,000,000). Do it with 12 tables .....You seem to have the same columns in each table so instead of[code]ad_brac ch_brac ad_neck--------- --------- ---------id id iddescrip descrip descripprice price pricestockcount stockcount stockcount[/code]have[code]Product Prod_type--------- -----------id typeIDtypeID typedescdescrippricestockcount[/code] Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.