Re-query The Query Results

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
transfield
Forum Commoner
Posts: 34
Joined: Wed Feb 22, 2006 6:00 am

Re-query The Query Results

Post by 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.

Code: Select all

<?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);  
?>
User avatar
Mordred
DevNet Resident
Posts: 1579
Joined: Sun Sep 03, 2006 5:19 am
Location: Sofia, Bulgaria

Post by Mordred »

Add AND (id != '$checkbox') to the WHERE clause of query4? (after rewriting it to select, not insert-select of course)
transfield
Forum Commoner
Posts: 34
Joined: Wed Feb 22, 2006 6:00 am

Post by transfield »

Sorry Mordred, I did not show you the actual flow of my code. Therefore I think your proposal will not work. This is how my full code looks like:-

Code: Select all

<?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...
?>
User avatar
Mordred
DevNet Resident
Posts: 1579
Joined: Sun Sep 03, 2006 5:19 am
Location: Sofia, Bulgaria

Post by Mordred »

So you need the query4 as it is as well. That's okay, query once with query4 (as select, not insert-select), then query again with query5 = query4 + checkboxes. I don't see any LIMIT clauses, but I hope you know what you are doing ;) If query4 is slow, and you're afraid of having to do it again with query5, you'd better optimise it anyway. Even without further optimisation, you can avoid requerying (with query5) altogether, by just looping through the data from query 4 and getting aside the rows that have the curresponding checkbox unchecked (practically doing query5 "by hand").

There is a security issue with $_POST['boxes'], it can be used for sql injection (also check every variable like $Text_Box_1 etc. if it is properly escaped with mysql_real_escape_string)
transfield
Forum Commoner
Posts: 34
Joined: Wed Feb 22, 2006 6:00 am

Post by transfield »

Thanks for your reply, Mordred. Sorry once again. There's one more thing I forgot to tell you.

Now going by your suggestion, I need to key in the search criteria twice(into my html form) in order to re-query the results of $query4 using $query5. I do not want to key in the search criteria twice. This is the reason why I created a temporary table. I trust you understand what I'm talking about.

My intention is:-
1. To key in the search criteria into the html form once.
2. Hit the Submit botton 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 would imagine that the search results of $query2 needs to be captured together with the user's IP address or session id in the temporary table but I don't know how to implement this.

Thank you for your patience.
User avatar
Mordred
DevNet Resident
Posts: 1579
Joined: Sun Sep 03, 2006 5:19 am
Location: Sofia, Bulgaria

Post by Mordred »

Hint: The search criteria from step 1 can be put into hidden inputs and passed to the submit_condo form, so step 6 would know the criteria AND the check boxes.
transfield
Forum Commoner
Posts: 34
Joined: Wed Feb 22, 2006 6:00 am

Post by transfield »

Sorry I don't understand what you mean. Thanks for your help anyway. I give up!
User avatar
Mordred
DevNet Resident
Posts: 1579
Joined: Sun Sep 03, 2006 5:19 am
Location: Sofia, Bulgaria

Post by Mordred »

Let's pretend the first search has only one box, 'text_box_1', the one that you use with $Text_Box_1 in your example. You should have something like $Text_Box_1 = $_POST['text_box_1'], right?

The user fills it, and clicks submit, queries 2-4 fire and print another form which has the condo_button. In that form, you put this:

Code: Select all

<INPUT type="hidden" name="text_box_1" value="<?php echo $Text_Box_1 ?>">
Now, when this form is submitted, it will have not only the checkboxes, but $_POST['text_box_1'], which will carry the original search box with it. So you have $Text_Box_1 AND the $_POST['boxes'] so you can build a query5 which is exactly like query4 ('cause you have $Text_Box_1) but has more clauses in the WHERE part to account for the boxes.

Is it clearer now?
Post Reply