how to evaluate the contents of a query and remove some?

[marcnyc]

Hello I have this piece of third party code which I am trying to change:

	$query = "SELECT id, format_type FROM reviews2_format ORDER BY format_type ASC";

$result = mysql_query($query, $connection);
echo ('<select class="text" name="format">');

while ($r = mysql_fetch_object($result)) {
	echo ('<option');
	// ---------------------------
	if ( $id == $r->id )
		echo (' selected ');
	// ---------------------------	
	echo (' value="'.$r->id.'">'.$r->format_type.'</option>');
}

What I basically wanna do is look at what is output in the drop down menu created (which is what is contained in $r->format_type) and decide whether to put it or not put it in the drop down list... in particular I wanted to check if it starts with a certain sequence of letters and if it does NOT include it in the list... I tried the following but it doesn't work and I don't know where to go from here:

	$query = "SELECT id, format_type FROM reviews2_format ORDER BY format_type ASC";

$result = mysql_query($query, $connection);
echo ('<select class="text" name="format">');

while ($r = mysql_fetch_object($result)) {
	echo ('<option');
	// ---------------------------
	if ( $id == $r->id )
		echo (' selected ');
	// ---------------------------	
	if ( ereg('^book_',$r->format_type) ) {
	echo (' value="'.$r->id.'">'.$r->format_type.'</option>');
              }
}

Any help would be appreciated. Thanks

[Weedpacket]

Wouldn't it be easier make the check in the SQL query and allow only records where the format_type is NOT LIKE "thingy_%"?

Otherwise you'll have to build the <select> entity up in a string, and echo it in one piece at the end.

[planetsim]

Also maybe use

mysql_fetch_array() instead of mysql_fetch_object() they can sometimes if not planned appropriately be the same as a class variable and cause errors.

Im pretty sure there is speed difference to might check on that though.

[marcnyc]

Weedpacket I like your idea... can you give me a code example I can work with??

[marcnyc]

weedpacket, still out there?

[nlhowell]

SELECT * FROM table WHERE column1 LIKE {pattern}

{pattern} uses % to indicate a wild character. For example, "%and" matches hand and land but not "handy". "and%" matches "andy" but not "hand".

[marcnyc]

Does %and match quicksand as well?

[marcnyc]

Ok I tried replacing this line:

$query = "SELECT id, format_type FROM reviews2_format ORDER BY format_type ASC";

with this one:

$query = "SELECT id, format_type FROM reviews2_format WHERE format_type NOT LIKE {book_%} ORDER BY format_type ASC";

and I also tried with this one:

$query = "SELECT id, format_type FROM reviews2_format WHERE format_type NOT LIKE book_% ORDER BY format_type ASC";

but that didn't work.
The page returns:

Warning: mysql_free_result(): supplied argument is not a valid MySQL result resource in /home/virtual/site372/fst/var/www/html/reviews/admin/main.inc on line 136

and the drop down menu contains no items at all...

line 136 is this:
mysql_free_result($result);

what did I do wrong?

[Weedpacket]

The format_type is a string, right? So the value you're looking for should be written in the query in quotes.

[lostarts]

$query = "SELECT id, format_type FROM reviews2_format WHERE format_type NOT LIKE 'book_%' ORDER BY format_type ASC";

[marcnyc]

that worked great - thanks a lot to the both of you...

just one additional question: is there a syntax that will allow me to exclude multiple patterns?

in other words what would be the correct way to write something that if I had to guess (and I am guessing) I would write for example like this?:

$query = "SELECT id, format_type FROM reviews2_format WHERE format_type NOT LIKE 'book%' & NOT LIKE 'something_else%' ORDER BY format_type ASC";

Thanks

[lostarts]

$query = "SELECT id, format_type FROM reviews2_format WHERE format_type NOT LIKE 'book%' AND NOT LIKE 'something_else%' ORDER BY format_type ASC";

[marcnyc]

unfortunately the AND NOT syntax did not work... does anybody know the correct syntax to do this?
Thanks

[nlhowell]

Dammit, people. I get this alot; if you use an AND clause, that's like saying and in PHP. You wouldn't say:

if($x == "5" and != "4") 
{
     // do something...
}

The correct MySQL syntax:

SELECT id, format_type FROM reviews2_format WHERE format_type NOT LIKE 'book_%' AND format_type NOT LIKE 'something_else_%' ORDER BY format_type ASC

[marcnyc]

Thank you. This worked!