Mysql and PHP Join Query

[fluiduk]

Hi All

What is the best/quickest way to achieve this.

I have two tables, one call product_info which holds the products information and one call product_availability which contains the latest stock figures.

I need a join query that creates a table with all the of the information combined.

The common field between them is product_sku.

It needs to be very quick, as there will eventually be 50000 unique records in each table.

Any ideas? Or gentle nudges in the right direction

Thanks
Aaron

 

[elkdanger]

Something like:

SELECT PI.*, PA.stock FROM product_info PI, product_availability PA WHERE PA.product_sku = PI.product_sku;

..should do that for you. Note it's untested here, obviously.

 

[fluiduk]

Cool will give that a go, how would I then get that into a table, preferably without having to use repeats on PHP, thats what slows it right down.

Thanks
Aaron

 

[elkdanger]

Cool will give that a go, how would I then get that into a table, preferably without having to use repeats on PHP, thats what slows it right down.

Thanks
Aaron

Ah right, didn't read the question fully. Not entirely sure about the insert part of it, not without using some sort of loop anyway.

 

[fluiduk]

Ok

Would you mind showing me how you would do it with a loop kind of method? Will have to do for now. Im not too sure if the way I would do it would be the best

Thanks
Aaron

 

[Moe]

Try something like:

$query = 'SELECT PI.*, PA.stock FROM product_info PI, product_availability PA WHERE PA.product_sku = PI.product_sku';
$result = mysql_query($query) or die('Query failed: ' . mysql_error());

// Printing results in HTML
echo "<table>\n";
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
   echo "\t<tr>\n";
   foreach ($line as $col_value) {
       echo "\t\t<td>$col_value</td>\n";
   }
   echo "\t</tr>\n";
}
echo "</table>\n";
mysql_free_result($result);

edit: used CODE tag...

 

[fluiduk]

Sorted it now

Thanks guys

aaron

 

[oneilldo]

Cool will give that a go, how would I then get that into a table, preferably without having to use repeats on PHP, thats what slows it right down.

Thanks
Aaron

You could get that data into a table in SQL Server by doing select into.

In MySQL you will need to create the table first and then insert the data, I think the following will work

CREATE TABLE results
(field1FromProduct_info,
field2FromProduct_info,
theRestOfTheFieldsFromProduct_info,
stock
);
INSERT INTO results
SELECT PI.*, PA.stock FROM product_info PI, product_availability PA WHERE PA.product_sku = PI.product_sku;