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

    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;
    }

      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.

        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.

          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!

            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

              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.

                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

                  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.

                    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;

}
                      a year later

                      thats just the code i was looking for chyan thanks 😃

                        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.

                          Write a Reply...