Jump to content

Archived

This topic is now archived and is closed to further replies.

dragon2309

Queries - user customisable...!!!!

Recommended Posts

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">&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>[/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]

Share this post


Link to post
Share on other sites
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              id
descrip         descrip         descrip
price           price           price
stockcount      stockcount      stockcount[/code]

have

[code]Product          Prod_type
---------        -----------
id                typeID
typeID            typedesc
descrip
price
stockcount[/code]

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.