Re-query The Query Results

[transfield]

Hello,
I'm querying a Mysql database & displaying the results. My code works fine. I now want to re-query the original query results. Therefore, I devised a crude method of dumping the original query results into a temporary table & querying the temporary table. I realised that this is not a good way of re-querying the query results because if more than 1 person is querying my database at the same time, then the records in the temporary table will keep changing.

Therefore, how do I re-query my original query results in a better way?

My current code is below for you to gain a better understanding of what I'm currently doing.

Thanks for your help.

<?php
//the original query
$query2=("SELECT * FROM $table WHERE (full_add $r1 '%$Text_Box_1%' $r2 full_add $r5 '%$Text_Box_2%' $r3 full_add $r6 '%$Text_Box_3%' $r4 full_add $r7 '%$Text_Box_4%') AND (status = '$status') AND (size >= '$Text_Box_15' AND size <= '$Text_Box_16') ORDER BY price DESC $limit");

//I'm clearing the temporary folder of previous records
$query3=("DELETE FROM temp") or die(mysql_error());

//I'm inserting the query results from $query2 into the temporary folder
$query4=("INSERT INTO temp (id, date, full_add, status, size, price, price_psf) SELECT id, date, full_add, status, size, price, price_psf FROM $table WHERE (full_add $r1 '%$Text_Box_1%' $r2 full_add $r5 '%$Text_Box_2%' $r3 full_add $r6 '%$Text_Box_3%' $r4 full_add $r7 '%$Text_Box_4%') AND (status = '$status') AND (size >= '$Text_Box_15' AND size <= '$Text_Box_16') ORDER BY price DESC");

$result3=mysql_query($query3);
$result2=mysql_query($query2);
$result4=mysql_query($query4);

//I'm querying the temporary folder
$query5=("SELECT price FROM temp WHERE id != '$checkbox' ORDER BY price DESC");

$result5=mysql_query($query5);
?>

[laserlight]

Why not store the results of the original query, and then run the delete?

[transfield]

Thanks for your reply, laserlight.

Why not store the results of the original query, and then run the delete?

The temporary folder could contain some records of a previous query. If I store the results of the original query into the temporary folder without deleting whatever is in the temporary folder first, then when I query the temporary folder, my results would be inaccurate.

Anyway, I think I need to show you the full flow of my code for your better understanding. Sorry if it looks overwhelming, but it's well commented. Happy CNY to you :-)

<?php 
//the original query    

$query2=("SELECT * FROM $table WHERE (full_add $r1 '%$Text_Box_1%' $r2 full_add $r5 '%$Text_Box_2%' $r3 full_add $r6 '%$Text_Box_3%' $r4 full_add $r7 '%$Text_Box_4%') AND (status = '$status') AND (size >= '$Text_Box_15' AND size <= '$Text_Box_16') ORDER BY price DESC $limit");    

//I'm clearing the temporary folder of previous records    

$query3=("DELETE FROM temp") or die(mysql_error());    

//I'm inserting the query results from $query2 into the temporary folder    

$query4=("INSERT INTO temp (id, date, full_add, status, size, price, price_psf) SELECT id, date, full_add, status, size, price, price_psf FROM $table WHERE (full_add $r1 '%$Text_Box_1%' $r2 full_add $r5 '%$Text_Box_2%' $r3 full_add $r6 '%$Text_Box_3%' $r4 full_add $r7 '%$Text_Box_4%') AND (status = '$status') AND (size >= '$Text_Box_15' AND size <= '$Text_Box_16') ORDER BY price DESC");    

$result3=mysql_query($query3);    

$result2=mysql_query($query2);    

$result4=mysql_query($query4); 
//the table to display the results of $query2. Nothing abnormal here. 
?> 
<table width=700 border=1 height="0" style="border-collapse: collapse" bordercolor="#111111" cellpadding="0" cellspacing="0"> 
<tr bgcolor ="cyan"> 
<td width="375" align="center" bgcolor="#CCFFFF"><b><font face="arial" size="2.5">Raw Data</a></font></strong></td> 
<td width="50" align="center" bgcolor="#CCFFFF"><b><font face="arial" size="2.5">Size</font></strong></td> 
<td width="50" align="center" bgcolor="#CCFFFF"><b><font face="arial" size="2.5">Price</font></strong></td> 
<td width="100" align="center" bgcolor="#CCFFFF"><b><font face="arial" size="2.5">Tick Bad Data & Re-calculate</font></strong></td> 
</tr> 
<?php 
/*the code to display the results of $query2 in multi colour. Nothing abnormal here but 
please observe that I'm echoing a checkbox for every record displayed.*/ 
$color="1"; 
while($rows=@mysql_fetch_array($result2)){      

echo '<form action="" method="POST">'; 
if($color==1){ 
echo "<tr bgcolor='#CCFFFF'> 
<td align=left><font face=arial size=1.5>{$rows['full_add']}</td></font> 
<td align=center><font face=arial size=1.5>{$rows['size']}</td></font> 
<td align=center><font face=arial size=1.5>{$rows['price']}</td></font> 
<td align=center><input type='checkbox' name='boxes[]' value='{$rows['id']}'></td> 
</tr>"; 

$color="2"; 
} 

else { 
echo "<tr bgcolor='#99FFCC'> 
<td align=left><font face=arial size=1.5>{$rows['full_add']}</td></font> 
<td align=center><font face=arial size=1.5>{$rows['size']}</td></font> 
<td align=center><font face=arial size=1.5>{$rows['price']}</td></font> 
<td align=center><input type='checkbox' name='boxes[]' value='{$rows['id']}'></td> 
</tr>"; 

$color="1"; 
} 
} 
/*there is some code here to create a Submit button called submit_condo. I did not include 
this code because I don't think it will affect your understanding of the flow of events.*/ 
?> 
<?php 
//okay, this is where I'm querying the temporary table which I prefer not to do. 
$submit_condo = $_POST['submit_condo']; 
$checkbox = @implode("' and id != '", $_POST['boxes']); 

if(isset($submit_condo)) 
{ 
//connection to the mysql database 
$username="something"; 
$password="something_else"; 
$database="some_database"; 
$host="localhost"; 
mysql_connect ("$host","$username","$password"); 
mysql_select_db($database) or die( "Where's the database man?"); 

$query5=("SELECT price FROM temp WHERE id != '$checkbox' ORDER BY price DESC"); 
} 
$result5=mysql_query($query5); 
//the results of $query5 is displayed beyond this... 
?>

[laserlight]

Sorry, but what I do not understand is why you even need a temporary table in the first place.

[transfield]

Thanks for your reply, laserlight.

My intention is:-
1. To key in the search criteria into the html form once.
2. Hit the Submit button where $query2, $query3 and $query4 will run at the same time.
3. Display the search results of $query2 with checkboxes next to each record.
4. Manually tick some of the checkboxes which contain records that I dislike(for whatever reason).
5. Hit the submit_condo button to make $query5 run.
6. Display the results of $query5.

The temp table is to store the search results of $query2 so that I can re-query it with $query5.

[laserlight]

Instead of using a temporary table, save the results into an array, and perhaps store that array in a session.

[transfield]

Could you show me how to do that? I'm quite new to php. Thanks & have a good day, laserlight.