Query - user customisable - PHPBuilder Forums

Query - user customisable

dragon2309

hey hey everyone, how you all doing. Forst post here so go easy please. Heres the problem.

Background Info:

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.

The Problem:

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....

<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">&nbsp;</th>
       <th>Item Number</th>
       <th>Description</th>
       <th>Price</th>
       <th>Image</th>
       <th>Information</th>
     </tr>


</div>



 <tr>
   <td bgcolor="#FFECFF">&nbsp;</td>
   <th bgcolor="#FFECFF">&nbsp;</th>
   <th bgcolor="#FFECFF">&nbsp;</th>
   <th bgcolor="#FFECFF">&nbsp;</th>
   <th bgcolor="#FFECFF">&nbsp;</th>
   <th bgcolor="#FFECFF">&nbsp;</th>
   <th bgcolor="#FFECFF">&nbsp;</th>
   <th bgcolor="#FFECFF">&nbsp;</th>
   <th bgcolor="#FFECFF">&nbsp;</th>
   <th bgcolor="#FFECFF">&nbsp;</th>
   <th bgcolor="#FFECFF">&nbsp;</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">&nbsp;&nbsp;&nbsp;&nbsp;</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 &nbsp; to make it look good
if ($counter==1){
echo "    <td>&nbsp;</td>
           <td>&nbsp;</td>
           <td>&nbsp;</td>
           <td>&nbsp;</td>
           <td>&nbsp;</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>

The page is located at http://www.simplytrue.co.uk/stocklist/query_new.php

Please help me, i am at a loss as to what to do...

Thankyou, dragon2309

mourisj

$query = "SELECT FROM ad_brac WHERE Price='$price' AND StockCount > 0
UNION
SELECT FROM ch_brac WHERE Price='$price' AND StockCount > 0
UNION
SELECT * FROM ad_neck WHERE Price='$price' AND StockCount > 0";

dragon2309

is there anythign to explain to me about using this or does it simply join queries together, will it work in such a way where all data will be echoed intot he same table... or will i need to completely reconstruct my php pages to accomadate this...? thanks for you help and time

dragon2309

ok, UNION doesnt work, it says i have syntax error in the query, but i changed it around and even re-wrote it myself, still the same.... any hints....

dragon2309

Sxooter

Are these twelve tables basically the same, but for different categories? If so, you have a relational error on your hands, and need to make them one table and have a field in it that says which category it is. A union might give you the right answer, but the problem appears to be your table layout. Can we see your schema, or at least the important parts of it?

Now is the time to fix a bad schema, not a year after you've put it into production.

Plus it looks like you're making an unconstrained join. You might wanna look up a good tutorial on SQL on the net somewhere. Unconstrained joins tend to blow up fast (the size is equal to the number of rows in each table times the number of rows in the next table and so on)

dragon2309

oh man, i was afraid of that, yes, i have a lot of tables all for different categories and every table has identical field names... i cannot change the database design now, i have it all set up and working as it is, its just this new thing, is there no other way i can return results from multiple tables with the same fields....

dragon2309

Sxooter

The design is screwed up. If you had a database that was good (performance and behaviour wise) with unions you could limp along with unions til you could fix it.

With MySQL, you need to fix it, and the sooner the better. The more code you write to make this worse, the more of a 1,000 armed octopus it'll become. It'll be a maintenance nightmare everytime you want to add a category. with a proper layout, you'd just insert one row and bang, you'd have a new category. With this layout, every new category will touch every single page in your app. You'll be as busy as Doctor Who running the TARDIS.

Trust me, you HAVE to change it, and as soon as possible. Take a weekend and do it.

Sxooter

P.s. the only way to handle this right now is to query each table by itself, dump the results into a temp table and query that at the end. Fixing the schema is actually less work than trying to make it work as is.

dragon2309

ok, thanks for your help man, seeing as this project was just a one off and im not supposed to be doing it at all, i probably wont redo the whole thing. it works as it is and it is only for a small business. I'm only 17 years old and i manage the site by myself, big tasks are often hard to complete. Like this..... at current ive got user customisable queries sert up to this degree:

www.simplytrue.co.uk/stocklist/querytest.php

You can see how each category (only 3 atm, i havent finished it...) can get called up, by simply storing values in a PHP SELF function form. It works like that so its not a massive big deal that it be able to call from ALL tables...

Thanks, dragon2309 😃