[Resolved] SElect results

[zzz]

I'm having difficulty figuring out this issue:

I have an list of results displayed. I need to check a checkbox next to some results and then submit a page to get only the detailed results for selected items.

I can put each result unique ID in checkbox value but that's where i'm not sure how to proceed.

Also, how do I get the quiry right for selected results from db

WHERE id='2,4,6,7-13'

Lost...

[bradgrafelman]

I would split it up myself; don't think MySQL is interpreting that correctly:

"SELECT * FROM myTable WHERE id='2' OR id='4' OR id='6' OR (id BETWEEN '7' AND '13')"

Or something similar.

[zzz]

Well, the problem is that I do not know how many results will be selected and in which order/intervals...

I suppose I could send it into a loop and then draw each one at a time. Although I've never done that sort of things as well. I know it is possible, because I see similar functionality all over the web, just am having difficulty getting a good start.

😉

[bradgrafelman]

So do you have some table shown to a user with checkboxes, and they check the rows they want to edit/delete/whatever, and you're trying to build a query to SELECT those rows?

What is the name of the checkbox in the HTML form? You could do something like...

$query = 'SELECT * FROM MyTable WHERE ';
for($i=0; isset($_POST["checkbox_$i"]); $i++) {
if($_POST["checkbox_$i"] == "checked") $query .= "id='$i' OR";
}

$query = rtrim($query, 'OR ');
$result = mysql_query($query);

or something like that.

[zzz]

Doesn't it assume that my selection would consist of consecutive numbers?

I can call check box anything, say cID. The selection may be quite random. Also I just added a page break script, so now I need to think how to pass ids selected on the first page to the following and then submit them all at once.

[laserlight]

Suppose you're using name="checkbox[1]" for your checkboxes.
Assume also you have checked that $_POST['checkbox'] exists.
Then an improvement of bradgrafelman's code would be:

$query = 'SELECT * FROM MyTable WHERE id IN (';
$in = array();
foreach ($_POST['checkbox'] as $box) {
	$in[] = $box;
}
$query .= implode(',', $in) . ')';
$result = mysql_query($query);

EDIT:
actually, this is missing validation on $box.
You should check it.

[bradgrafelman]

Agreed about the validation bit. Make sure the magic_quotes_gpc is on ([man]get_magic_quotes_gpc[/man]) and use [man]addslashes[/man] accordingly.

Also: you learn something new everyday. I'd never used arrays in HTML form elements :p

[zzz]

Wow!

This all sounds great but I'm in a process of getting it all fit together in my mind.

So what I've got right now is a table where I output article titles from my database.
Next to each article I put a check box where I write each particular article's uniques ID:

<input type=checkbox name=aID value=".$ad->ads_id.">

So I assume at the end I'll put a submit button that will get checked values and place it into my quiry string, right?

I use a class to make calls to my db so my code may look a bit different. Normally, if I had one value, my code will look like this:

if ($articles = $db->get_results("SELECT * FROM myArticles WHERE art_id = $aID"))
	{
		foreach ($articles as $art)
			{
				echo $art->art_title."<p>".$art->art_summary;
			}
	}
	else {
		echo "No results";
	}

So given all the great suggestions should my code be looking somewhat like that?

 
if ($articles = $db->get_results("SELECT * FROM myArticles WHERE art_id IN (';
	$in = array(); 
	foreach ($_POST['aID'] as $box) { 
    	$in[] = $box; 
	} 
	$articles .= implode(',', $in) . ')';"))
	{
		foreach ($articles as $art)
			{
				echo $art->art_title."<p>".$art->art_summary;
			}
	}
	else {
		echo "No results";
	}

So as you can see I was not sure where and how to include magic_quotes_gpc and addslashes / stripsplashes.

I need help putting this masterpiece together!

[bradgrafelman]

You would do that before you pull in your variables from the $_POST array:

if (!get_magic_quotes_gpc()) {
  foreach($_POST as $key => $value) {
     ${$key} = addslashes($value);
  }
} else {
  foreach($_POST as $key => $value) {
     ${$key} = addslashes($value);
  }
}

That way no one can escape out of a SQL query and run malicious code.

EDIT: Also, your code example above has an error.

I'm not sure exactly how you were coding it, as I'm trying to escape out to lunch right now, but I'm hoping you can find it.

[zzz]

I use this class to simplify my life: http://justinvincent.com/home/docs/ezsql/ez_sql_help.htm

I think this is how it actually should be, right?

if (!get_magic_quotes_gpc()) { 
  foreach($_POST as $key => $value) { 
     ${$key} = addslashes($value); 
  } 
} else { 
  foreach($_POST as $key => $value) { 
     ${$key} = addslashes($value); 
  } 
}

if ($articles = $db->get_results("SELECT * FROM myArticles WHERE art_id IN ('; 
    $in = array(); 
    foreach ($_POST['aID'] as $box) {  

        $in[] = $box; 
    }  

    $articles .= implode(',', $in) . ')")) 
    { 
        foreach ($articles as $art) 
            { 
                echo $art->art_title."<p>".$art->art_summary; 
            } 
} 
else { 
	echo "No results"; 
}

Enjoy your lunch!

[bradgrafelman]

Notice all the red in your posted code? All of that means its inside a string! Not good.

Try this:

if (!get_magic_quotes_gpc()) {  

  foreach($_POST as $key => $value) {  

     ${$key} = addslashes($value);  

  }  

} else {  

  foreach($_POST as $key => $value) {  

     ${$key} = $value  

  }  

} 

$in =array();
foreach ($aID as $box) $in[] = $box;
$in = implode(',', $in);

$query = "SELECT * FROM myArticles WHERE art_id IN ( $in )";

if ($articles = $db->get_results($query))
    {  

        foreach ($articles as $art)  

            {  

                echo $art->art_title."<p>".$art->art_summary;  

            }  

}  

else {  

    echo "No results";  

}

or something similar. I'm not too savvy with the MySQL "IN" clause, so I don't know if they have to be surrounded by single quotes or not... in my code they were not. If they need to be, try to manipulate it yourself and fix that, otherwise, let me know.

I think what you were trying to do is start an if() statement but break into other PHP code to finish that if() statement. Can't do that.

EDIT: Fixed an error in my magic quotes code. By the way, if you're using my magic quotes bit of code, all elements in $POST[] are returned as variables as $(name of form field) and set to the value submitted (i.e. $POST['aID'] shouldn't be used; instead, $aID should be.) If this overwrites variables you have set, or would rather have all POST variables separated, you can prepend the variables, such as $POST_aID, etc.

[zzz]

Just today I was able to get to this issue and after a few trials it worked like a charm!

[bradgrafelman]

I'm glad to hear that 🙂

I stay subscribed to threads until they're marked "resolved", though I do occasionally go in and prune old subscriptions :p