delete mysql result rows?

eon_sol

I'm wanting some way to loop through the rows of a mysql result set and selectively delete certain rows. Can anyone help?

For example:

$res = mysql_query($query, $db);
while ($row = mysql_fetch_assoc($res)) {
if ($row['value'] == "something") {
// Some code to delete this row from the mysql result set?
} }

I'm wanting to use this code to filter out search results in a user database. For example, before I run mysql_num_rows on the result set I might want to delete rows of users who are under 13 years of age so I could then give you the number of users over 13. I know I could perform this particular example with a more precise SELECT operation (WHERE userage > 13) but I'm needing to filter results in much more complex ways that are beyond the scope of MySQL.

Thank you for your time and consideration...

chyan

you dont need loop for that, just

$query="delete from your table where userage < 13"; //this one will delete all user below 13 in ur table
if (!mysql_query($query,$conn)){
echo mysql_error();
exit;
}

eon_sol

No, as noted, I am attempting to delete from the query result set, not from the actual table in MySQL. Or in other words, in this example, I'm deleting rows from $res and not from the MySQL table.

Again, I realize that my example could easily be accomplished with MySQL queries but what I'm actually doing in my code is much more complex and is beyond the scope of MySQL. IE, I'm running a series of calculations on each row to determine if I need to exclude it or not. These calculations are outside of anything that can be done in MySQL.

Thanks for any ideas.

laserlight

Perhaps work the other way round - if the result row is accepted, save it in another array, then once the loop ends use that array instead.

Or you could save space by storing the given keys, and then use only those rows whose keys are saved.

eon_sol

Thank you, laserlight. I'd thought of that but was still hoping for something simpler. Unless there is some easy way to get a mysql result set into an array? I'd imagined it would have to be a compound array. IE, $arraykey

I do like your idea about storing the keys for the mysql result rows. Could you give an example of how that would be accomplished? I'm not sure how I'd get a key from a certain row.

Thanks!

chyan

why you dont just create 2 query for that
first make query to select all your record
and then make query to select your record where the users age is over 13

then you can compare the result for both query and get the number of user over
and below 13

eon_sol

chyan: i'm afraid i've failed to explain the problem clearly enough. forget the whole problem of finding people who are over 13.

the very problem is running my query to begin with. i'm having trouble with this query because i'm needing to run a series of complex calculations on each row to determine whether or not i want to select it. specifically, i'm running distance calculations based on zip codes. for each row, i need to calculate the user's distance from my location based upon their zip code and then decide whether or not that user is too far away from me to be included in the result set. that's something a little too complex to do in MySQL so i need some way to filter through the actual result set.

thanks for the great ideas so far.

chyan

sorry about my misunderstanded about ur problem
but i'm still little bit confuse about your question to delete result set
cause i think no need to delete the result set since you have an option
whether you want to display it or not

while($row = mysql_fetch_array($result)) {
$value= $row["zipcode"];

if($value > your calculation){ // check whether you want to accept it based your zip code
$value=""; // if not accepted, give the $value null value, so will not be displayed
}

so the idea is simply give the null value to whatever data you dont want to display based on your calculation.

sorry it just my opinion, please explain me if im still wrong again

eon_sol

i resolved this by using laserlight's suggestion of loading the mysql result rows into an array and then performing my filtering on that array.

thanks to all for their help.

chyan

just for addition

there are another way to do the same task and give the same result

while($row = mysql_fetch_array($result)) { 
$value= $row["zipcode"]; 


if($value > your calculation) //check the condition for get the data you dont want to display
{ 
continue; // skip the data when condition above is true and going to check next data
} 

echo $value;

}

homer09001

thats just the code i was looking for chyan thanks 😃

Roger_Ramjet

eon sol wrote:
but I'm needing to filter results in much more complex ways that are beyond the scope of MySQL.

I doubt that. Most people underestimate the flexibility of the SQL query language, and it's power. Basically, if a set can be defined in say php then it can be defined in sql, and hence in mysql.

So you want to flag how far each result is from a given base: "i need to calculate the user's distance from my location based upon their zip code and then decide whether or not that user is too far away from me to be included in the result set".

So how are you determining that in code?
Somewhere you must be storing the data that you use to calculate the distance between zip code; where?

Post that code and the data storage layout, and I'll show you how to do it in a single query.