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 Code:

<?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 which I prefer not to do
$query5=("SELECT price FROM temp WHERE id != '$checkbox' ORDER BY price DESC"); 

$result5=mysql_query($query5); 
?>

[DJMaze]

Why are you inserting prices into a temp table?

[redhead]

You could use sessions and store your query results into sessions variabels, then when you want to re-query, you fetch them from the session variabel, plus whatever you request for on the re-query.

[transfield]

DJmaze, thanks for your reply. Here's what I want to achieve:-
1. To key in some search criteria into a html form once(I've not included the code for this because it's just a simple form).
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(I've not included the code for this as well).

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.

PHP Code:

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

I was adviced on another forum to save the query results into an array, and store that array in a session but I don't know how to write the code.

Thank you for your help. Much appreciated.

[transfield]

Originally Posted by redhead

You could use sessions and store your query results into sessions variabels, then when you want to re-query, you fetch them from the session variabel, plus whatever you request for on the re-query.

Could you show me how to write the code, redhead?

[redhead]

Perhaps somethings along the lines of:

PHP Code:

<?php
session_start();
$result = mysql_query("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"); 
$_SESSION['result']=array();

while($rows=@mysql_fetch_array($result))
        $_SESSION['result'][]=('id'=>$row['id'], 'date'=>$row['date'], 'full_add'=>$row['full_add'],
                              'status'=>$row['status'], 'size'=>$row['size'], 'price'=>$row['price'], 'price_psf'=>$row['price_psf']);
?>

Now you have the results gathered in these $_SESSION variabels, so when ever you want to use them, you'd do something like:

PHP Code:

<?php
session_start();
foreach ($_SESSION['result'] as $row)
    mysql_query("INSERT INTO table (id, date, full_add, status, size, price, price_psf) VALUES ($row['id'], $row['date'], $row['full_add'], $row['status'], $row['size'], $row['price'], $row['price_psf']");
>?

Or however you want to use the stored values....

[transfield]

Hi redhead,
I'm getting a unexpected T_DOUBLE_ARROW error on the same line as

PHP Code:

$_SESSION['result'][]=('id'=>$row['id'], 'date'=>$row['date'], 'full_add'=>$row['full_add'], 


How do I debug this?

Thanks.

[DJMaze]

ever used array() before?

[transfield]

Originally Posted by DJMaze

ever used array() before?

No.

[redhead]

One thing which is specified for array() makes me think of this small alteration

PHP Code:

$_SESSION['result'][]=array('id'=>...) 


[transfield]

Originally Posted by redhead

One thing which is specified for array() makes me think of this small alteration

PHP Code:

$_SESSION['result'][]=array('id'=>...) 


Thanks redhead. Okay this version does not give me any errors anymore. Now how do I use this code to replace $query5?

[redhead]

If you wanted to get the prices and use them in your select later on, it would be something like this:

PHP Code:

<?php
session_start();
/* eliminate every ID we've chosen */
foreach ($_POST['boxes'] as $id){
  /* temporary storage of the array of rows */
  $result = $_SESSION['result'];
  /* clean any previus result stored */
  $_SESSION['result'] = array();
  /* keep only IDs and prices for the IDs not matching the chosen */
  foreach ($result as $row)
    if ($row['id'] != $id)
      $_SESSION['result'][] = array('id'=>$row['id'], 'price'=>$row['price']);
  }
$result=$_SESSION['result'];
?>

And now you all the prices stored in $result, but why only select the price, since you have every available info located, you could gather anything which is nessesary..
Oh, another thing, the results are not sorted in any way.. But it should be fairly easy to create a simple sorting of the result array...

[transfield]

Could you tell me where am I supposed to insert this code? I don't understand what I'm looking at. How is this code supposed to replace $query5?

And now you all the prices stored in $result

I echoed $result and all I get is the word Array. So where are the prices?

[DJMaze]

Who wrote the code in the first place?
It sounds it's not your PHP code at all.

Are you that familiar with sql but not with php?

[redhead]

If you want to display everything within an object, like an array is, you need to use print_r()