[SOLVED] Mysql results into array??

[perl2php]

I am trying to build a little program that will compare the contents of one MySQL table to another..

 

Apparently, it is not possible to nest one mysql query within another...

 

For example:

 

$sql = mysql_query("SELECT * FROM table1") or die(mysql_error());

// CHECK TO SEE IF ANY STOCK NUMBERS IN TABLE1 ARE IN TABLE2

while($row = mysql_fetch_array($sql)) { 

$q_stock = $row['stock'];

$query = mysql_query("SELECT * FROM table2 WHERE stock = $q_stock") or die (mysql_error());
$number= mysql_num_rows($query);

// IF SO, DO NOTHING

if ($number != 0) { }

// IF NOT, ADD STOCK NUMBER AND INFO TO ADD_LOG DATABASE
else {
mysql_query ("INSERT INTO add_log (stock, method) VALUES ('$q_stock', 'user')");
}
}

 

I take it mysql doesn´t like you to try and query it while you are cycling through it´s results..

 

So then how would I compare 2 tables then? Is there a way to load all entries of a table into a php array?

 

Thanks

[teng84]

$query = mysql_query("SELECT * FROM table2 WHERE stock = $q_stock") or die (mysql_error());

 

$array=mysql_fetch_assoc($query);

[cooldude832]

Fyi this is a really poor logical build because you want to minimize queries adn you are having a chance for millions of queries

[perl2php]

That´s what I was thinking too.. how then would you suggest comparing two tables against each other?

 

Besides.. it appears that you cannot nest all those queries inside each other.. Mysql doesn´t allow it.

 

 

 

[cooldude832]

well what exactly are we trying to do here

[perl2php]

Simply put...

 

I need to take the entire contents of  table1 and compare it to the contents of table2.

 

If table 1 has a stock number that table 2 doesn´t, that stock number is inserted into another table called add_log.

 

Does that make sense?

 

Both tables have the same structure.. Columns are: STOCK, COLOR, PRICE, QUANTITY -- (Like my post earlier about file reading)

 

 

 

 

[Yesideez]

SELECT * FROM table2 WHERE stocknumber NOT IN (SELECT * FROM table1)

 

I'm not that great with MySQL when it comes to using more than one table in the same query but I think that's the sort of thing although I think you'll have to change a couple field names around.

[teng84]

$query = mysql_query("SELECT stock FROM table1 WHERE stock = $q_stock") or die (mysql_error());

$array=mysql_fetch_assoc($query);

 

$query = mysql_query("SELECT * FROM table1 WHERE not stock in(".implode(',',$q_stock.)"") or die (mysql_error());

$array=mysql_fetch_assoc($query);

 

// from the query you will have the record that is not in this table I may have some error for its not tested but the the idea i think you need

 

[cooldude832]

alright well since we are comparing stock numbers lets do a bit of optimization on your query

try this its untested:

<?php
$table1 = mysql_query("SELECT `stock` FROM table1") or die(mysql_error()); //Gets all the Stocks in Table 1
$table2 = mysql_query("SELECT `stock` FROM table2") or die(mysql_error()); //Gets all the Stocks in Table 2
while ($row = mysql_fetch_array($table1){
$stocks1[] = $row['stock'];
}
while ($row = mysql_fetch_array($table2){
$stocks2[] = $row['stock'];
}
$compare = array_intersect_assoc($stocks2,$stocks1); //This will return a new array containing all the matches in table 2 to table 1 with the keys matching from stocks2
//Now that we have all the matches lets remove them
foreach ($compare as $key => $value) {
array_splice($stocks2,$key); //This will remove each entry that was found to match table 1
}
//Now stocks2 is an array with all unmatched keys so lets do some insertion
foreach($stocks2 as $value) {
mysql_query ("INSERT INTO add_log (stock) VALUES ('$value')") or die(mysql_error());
}
?>

 

[teng84]

alright well since we are comparing stock numbers lets do a bit of optimization on your query

try this its untested:

<?php
$table1 = mysql_query("SELECT `stock` FROM table1") or die(mysql_error()); //Gets all the Stocks in Table 1
$table2 = mysql_query("SELECT `stock` FROM table2") or die(mysql_error()); //Gets all the Stocks in Table 2
while ($row = mysql_fetch_array($table1){
$stocks1[] = $row['stock'];
}
while ($row = mysql_fetch_array($table2){
$stocks2[] = $row['stock'];
}
$compare = array_intersect_assoc($stocks2,$stocks1); //This will return a new array containing all the matches in table 2 to table 1 with the keys matching from stocks2
//Now that we have all the matches lets remove them
foreach ($compare as $key => $value) {
array_splice($stocks2,$key); //This will remove each entry that was found to match table 1
}
//Now stocks2 is an array with all unmatched keys so lets do some insertion
foreach($stocks2 as $value) {
mysql_query ("INSERT INTO add_log (stock) VALUES ('$value')") or die(mysql_error());
}
?>

 

 

no need to use the loop the result of fetch array or assoc is an array  my example may be

not enough as well as yesidezz example but its shorter and faster

[cooldude832]

teng what did you change??

[teng84]

i didnt change any thing i just want to say that maybe your idea works but i guess its a long process \

1.) why need a loop fetch is already an array

2.) using the not in operator will return the diff between the two table

3.)it save multiple lines

4.)its also faster than looping over and over again

 

hope that helps remember this is what my professor told be

theres good and theres better

cheers

[cooldude832]

alright so this saves 2 loops

<?php
$table1 = mysql_query("SELECT `stock` FROM table1") or die(mysql_error()); //Gets all the Stocks in Table 1
$table2 = mysql_query("SELECT `stock` FROM table2") or die(mysql_error()); //Gets all the Stocks in Table 2
$stocks1 = mysql_fetch_array($table1);
$stocks2 = mysql_fetch_array($table2);

$compare = array_intersect_assoc($stocks2,$stocks1); //This will return a new array containing all the matches in table 2 to table 1 with the keys matching from stocks2
//Now that we have all the matches lets remove them
foreach ($compare as $key => $value) {
array_splice($stocks2,$key); //This will remove each entry that was found to match table 1
}
//Now stocks2 is an array with all unmatched keys so lets do some insertion
foreach($stocks2 as $value) {
mysql_query ("INSERT INTO add_log (stock) VALUES ('$value')") or die(mysql_error());
}
?>

 

There is probably a way to remove all the matches in the same step you find the matches I just can't think of it

[perl2php]

Thanks everyone!

 

That is a lot of replies really quick. I will try them in a bit.

 

I will get back to you.

 

 

[teng84]

$query = mysql_query("SELECT stock FROM table1 WHERE stock = $q_stock") or die (mysql_error());

$array=mysql_fetch_assoc($query);

 

$query = mysql_query("SELECT * FROM table1 WHERE not stock in(".implode(',',$q_stock.)"") or die (mysql_error());

$array=mysql_fetch_assoc($query);

 

// from the query you will have the record that is not in this table I may have some error for its not tested but the the idea i think you need

 

 

that was the code i posted may theres an error for i dont test it but it desnt have a loop

but from that you can have the difference btw the two tables in an array

 

cooldude dont think im doing this to hurt you but i do this to show my ideas

mmm

 

php and mysql is a tag team for me so you have to do both dont rely on php use mysql query to have a better coding and faster loading of the site

[cooldude832]

That could work, didn't think about using the results from table 1 in the query for table 2, probably the best idea out there then you return the array of unque values and just use the foreach loop to insert them into the log table

[teng84]

 

cool

 

this is what forums all about 

[cooldude832]

okay so try this

<?php
$table1 = mysql_query("SELECT `stock`, `user` FROM table1") or die(mysql_error()); //Gets all the Stocks in Table 1
$row = mysql_fetch_array($table1);
$table2 = mysql_query("SELECT `stock`, `user` FROM table2 WHERE stock != $row['stock'] && user != $row['user']") or die(mysql_error()); //Gets all the Stocks in Table 2
$row = mysql_fetch_array($table2)
foreach($row as $value) {
mysql_query ("INSERT INTO add_log (stock, method) VALUES ('$value['stock'],$value['user']')") or die(mysql_error());
}
?>

[rameshfaj]

Yes I think the good way is to store the value of the result of first query in an array and then comparing them with the result of the second query!

 

[teng84]

you can also query the tables then use the array_diff just and additional info i guess

[perl2php]

Ding! Ding! Ding!

 

We have a winner! LOL

 

It seems the most painless way of accomplishing this is Yesideezś method..

 

It worked beautifully. I never knew you could combine two selects in one statement like that.

 

Thanks everyone! I will be able to accomplish so much more now.