[Resolved] for loop problem

[tuf-web_co_uk]

hi,

i'm trying use a for loop to stop certain records being shown from a query

if my array contains something then i want it to output the necessary sql code to stop it from retrieving those results

eg.
$array = array('XBOX');

then my sql query should be

SELECT DISTINCT platformFROMcheatsWHEREplatform` != 'XBOX'

i ran this through phpmyadmin and it worked fine

however when i try to use this script to do the same thing it doesnt work.

<?
function exclude($whereex) {
	$exc_list = array('XBOX');
	if (count($exc_list) != 0) {
		if ($whereex) {
		 	return "AND \n";
		}
		else {
			return "WHERE \n";
		}
		for ($i=0;$i<count($exc_list);$i++) {
			return "`platform` != ".$exc_list[$i]."'\n";
			if (($i-1) < count($exc_list)) {
				return " AND ";
			}
		}
	}
}

$sql = "SELECT DISTINCT `platform` FROM `cheats` ".exclude(FALSE);

?>

when i check my source i get this error

SELECT DISTINCT platform FROM cheats WHERE
You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

but i'm not quite sure where i'm going wrong.

any help appreciated

Jon

[dnast]

Once you return a value the function stops executing. So that's why you only get "WHERE".

It should me more along the lines of this:

<?
function exclude($whereex) {
    $exc_list = array('XBOX');
    if (count($exc_list) != 0) {
        if ($whereex) {
            $string = "AND \n";
        }
        else {
            $string = "WHERE \n";
        }
        for ($i=0;$i<count($exc_list);$i++) {
            $string .= "`platform` != ".$exc_list[$i]."'\n";
            if (($i-1) < count($exc_list)) {
                $string .= " AND ";
            }
        }
        return $string;
    }
}

$sql = "SELECT DISTINCT `platform` FROM `cheats` ".exclude(FALSE);

?>

[drawmack]

<?
function exclude($exc_list) {
    if(!is_array($exc_list) {$exc_list = array($exc_list);}
    if (count($exc_list) != 0) {
       $wc = "WHERE platform NOT IN ('" . implode("','",$exc_list) . "')";
    }
}

$exclude = array('XBOX'):
$sql = "SELECT DISTINCT `platform` FROM `cheats` ".exclude($exclude);

?>

[tuf-web_co_uk]

<?

function exclude($exc_list, $whereex) {

if(!is_array($exc_list)) {
	$exc_list = array($exc_list);
} 

if (count($exc_list) != 0) {
	$wherev = "WHERE";
	if ($whereex) {
		$wherev = "AND";
	} 
$wc = $wherev." `platform` NOT IN ('" . implode("','",$exc_list) . "')"; 
} 
return $wc;
} 

$exc_list = array("XBOX");

$sql = "SELECT DISTINCT `platform` FROM `cheats` ".exclude($exc_list, FALSE);
ech $sql;
?>

this shows

SELECT DISTINCT platform FROM cheats WHERE platform NOT IN ('')

i'm guessing its not getting anything from the array

any ideas why?

[tuf-web_co_uk]

i also tried using

<?
function exclude($whereex) { 
    $exc_list = array("XBOX"); 
    if (count($exc_list) != 0) { 
        if ($whereex) { 
            $string = "AND \n"; 
        } 
        else { 
            $string = "WHERE \n"; 
        } 
        for ($i=0;$i<count($exc_list);$i++) { 
            $string .= "`platform` != '".$exc_list[$i]."'\n"; 
            if ($i < count($exc_list)) { 
                $string .= " AND "; 
            } 
        } 
        return $string; 
    } 
}

$sql = "SELECT DISTINCT `platform` FROM `cheats` ".exclude(FALSE);
echo $sql;
?>

but got this error

SELECT DISTINCT platform FROM cheats WHERE
platform != 'XBOX'
AND You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 3

[dnast]

if ($i < count($exc_list))

should be

if (($i + 1) < count($exc_list))

[drawmack]

actually that if statement is that reminates when $i == count($whatever) so it shouldn't be there at all as it is wasting clock ticks and nothing else.

Also take the quotes off of the field name in your where clause

[dnast]

It actually does something when you change $i to $i + 1 😉

It checks ahead to see if the loop will continue, and if so, sticks an "AND" in there.

[tuf-web_co_uk]

thanks for the advice the post from dnast worked, thank you

just to say that i put quotes round my field names so that i dont accidently use a field which is in the disallowed names

thanks for the help.

[AstroTeg]

Untested, but this should work:

function exclude($whereex) 
{
	$sql = '';
	for($i = 0; $i < count($whereex); $i++)
	{
		if($sql != '')
			$sql .= ' AND ';
		$sql .= "platform != '" . $whereex[$i] . "'";
	}  // end for($i = 0; $i < count($whereex); $i++)

if($sql != '')
	$sql = ' WHERE ' . $sql;
return $sql;
}  // end function exclude($whereex) 

If $whereex is an exclude list, then you might try something a bit simplier such as: (again, untested - check for quote and comma placement to validate they're getting placed in the right spots)

$sql = '';
$sql = implode('", "', $whereex);
if($sql != '')
  $sql = ' WHERE platform NOT IN ("' . $sql . '")';
return $sql;

[AstroTeg]

Originally posted by tuf-web.co.uk
thanks for the advice the post from dnast worked, thank you

Oops - I'm slow and missed the fact you got this fixed. Cool.

[tuf-web_co_uk]

the value $whereex tells the function whether or not the WHERE statement has already been used, if it has then the sql code will be AND platform != 'PlatformName'