My Php Sql statement is not working

[brown2005]

SELECT id, url 
FROM domains d
INNER JOIN advertising a ON d.id = a.value
WHERE a.store = '. $_SESSION[store] .'
AND (d.id NOT IN ('. implode(',', $_SESSION[numbers]) .')) 
ORDER BY RAND()
LIMIT 1'

can anybody help with why this code is not working please? It does not print anything when the

 

AND (d.id NOT IN ('. implode(',', $_SESSION[numbers]) .')) 

 

line is in it, but does when taken out? 

 

Thanks in advance

Edited June 16, 2013 by brown2005

[ginerjm]

I presume that you are not doing any error checking when you execute this query, because I'm sure it will fail to run.

 

$qrslts = MySQL_query($q);

 

if (!$qrslts)

{

  echo "Error running query - error msg is: ".MySQL_error();

  exit();

}

 

should catch this.

 

Meanwhile - your query will only return two fields - id & url.

 

Also you session vars are not entered properly.  S/b   $_SESSION['index']

[Barand]

Just because query returns no results does not mean it isn't working. It just means it didn't find any rows that match the WHERE criteria

[brown2005]

Just because query returns no results does not mean it isn't working. It just means it didn't find any rows that match the WHERE criteria

 

the query is not actually working as I looked at source code and everything after the query is not there so there is something wrong with the query? 

[Barand]

see what mysql_error() returns after executing the query. also echo($q) to see what was executed

[brown2005]

see what mysql_error() returns after executing the query. also echo($q) to see what was executed

 

if (!$stmt) {

   echo "\nPDO::errorInfo():\n";

   print_r($db->errorInfo());

}

 

I have the following but it does not output anything? Is this the proper way to error handle with pdo? 

[ginerjm]

It sure would be helpful to actually see the code around this query process.  Not the whole script but the part that we are trying to debug for you.

[brown2005]

It sure would be helpful to actually see the code around this query process.  Not the whole script but the part that we are trying to debug for you.

 

if(!isset($_SESSION['numbers'])){ $_SESSION['numbers'] = array(); } 


SELECT id, url 
FROM domains d
INNER JOIN advertising a ON d.id = a.value
WHERE a.store = '. $_SESSION[store] .'
AND (d.id NOT IN ('. implode(',', $_SESSION[numbers]) .')) 
ORDER BY RAND()
LIMIT 1'


array_push($_SESSION['numbers'], $id);

right I think I have the problem:

 

if(!isset($_SESSION['numbers'])){ $_SESSION['numbers'] = array(); }

 

this like this does not work? but if I have say

 

if(!isset($_SESSION['numbers'])){ $_SESSION['numbers'] = array(1); }

 

then it does work, so how can I edit the script to make it work without there being anything in the array to start, please.

 

Thanks

Edited June 17, 2013 by brown2005

[Barand]

What does echo $q give.

 

If $_SESSION['numbers'] is empty then the query will look like

 

... NOT IN () ...

 

so try

if(!isset($_SESSION['numbers'])){ $_SESSION['numbers'] = array(0); } 

so you get

 

... NOT IN (0) ...

 

and avoid a syntax error

[brown2005]

What does echo $q give.

 

If $_SESSION['numbers'] is empty then the query will look like

 

... NOT IN () ...

 

so try

if(!isset($_SESSION['numbers'])){ $_SESSION['numbers'] = array(0); } 

so you get

 

... NOT IN (0) ...

 

and avoid a syntax error

 

thanks for the reply. The query needs to be empty at the start, so is there a way I can rewrite the mysql to say like

if(count($_SESSION['numbers']) === 0){

}else{

AND (d.id NOT IN ('. implode(',', $_SESSION['numbers']) .'))

}

Edited June 17, 2013 by brown2005

[ginerjm]

You could use a var to hold the "and" clause and just leave the var empty when you need to.

 

It would still be nice to see the actual code - you gave us but one more line when I asked before.  Apparently somewhere you are using a var $q which Barand has asked to see, but I haven't seen that part of the code.

[brown2005]

$_SESSION['selmgec'] = 1; 
$_SESSION['member'] = 1;


if(!isset($_SESSION['numbers'])){ $_SESSION['numbers'] = array(); } 


if(count($_SESSION['numbers']) > 0){


$stmt = $db->query('SELECT id, url  
FROM domains d
INNER JOIN advertising a ON d.domains_id = a.advertising_value
WHERE a.advertising_selmgec = '. $_SESSION['selmgec'] .'
AND advertising_type = "1"
AND (d.domains_id NOT IN ('. implode(',', $_SESSION['numbers']) .'))
ORDER BY RAND()
LIMIT 1');
}else{


$stmt = $db->query('SELECT id, url  
FROM domains d
INNER JOIN advertising a ON d.domains_id = a.advertising_value
WHERE a.advertising_selmgec = '. $_SESSION['selmgec'] .'
AND advertising_type = "1"
ORDER BY RAND()
LIMIT 1'); 


} 


while($row = $stmt->fetch(PDO::FETCH_ASSOC)){


$id = $row['id'];
$url = $row['url']; 


if($stmt->rowCount()>0){
echo' <div id="container">'. $id .' - '.$url.'</div><br />';
}else{
echo' <div id="container">No results exsist. Please edit your filter.</div>';
}


array_push($_SESSION['numbers'], $id);
asort($_SESSION['numbers']);
print_r(implode(' - ', $_SESSION['numbers']));


if(count($_SESSION['numbers']) > 5){ 


$stmt2 = $db->prepare("INSERT INTO numbers(numbers_id,numbers_selmgec,numbers_member,numbers_date,numbers_one,numbers_two,numbers_three,numbers_four,numbers_five,numbers_six) 
VALUES('',:field1,:field2,CURRENT_DATE(),:field3,:field4,:field5,:field6,:field7,:field8)");


$stmt2->execute(array(':field1' => $_SESSION['selmgec'],
':field2' => $_SESSION['member'],
':field3' => $_SESSION['numbers'][0],
':field4' => $_SESSION['numbers'][1],
':field5' => $_SESSION['numbers'][2],
':field6' => $_SESSION['numbers'][3],
':field7' => $_SESSION['numbers'][4],
':field8' => $_SESSION['numbers'][5]));


unset($_SESSION['numbers']); 


} 


} 

that is my code now, which works the way I want

[brown2005]

wondering if anyone could help me...

$stmt2->execute(array(':field1' => $_SESSION['selmgec'],
':field2' => $_SESSION['member'],
':field3' => $_SESSION['numbers'][0],
':field4' => $_SESSION['numbers'][1],
':field5' => $_SESSION['numbers'][2],
':field6' => $_SESSION['numbers'][3],
':field7' => $_SESSION['numbers'][4],
':field8' => $_SESSION['numbers'][5])); 

how can I sort the array before inserting in the database? 

[ginerjm]

sort by what?

[brown2005]

sort by what?

 

asort($_SESSION['numbers']);

print_r(implode(' - ', $_SESSION['numbers']));

 

well I have that which sorts the numbers in ascending order, but when they are inserted into the database they are in the order they were inserted into the array using 

 

array_push($_SESSION['numbers'], $id);

Edited June 17, 2013 by brown2005

[ginerjm]

The php manual has a great page on all the different sorts available. 

 

http://us.php.net/manual/en/array.sorting.php

 

 

You have chosen assort which is intended for associative arrays, but I think your actual array is not an associative one.  I think you want to sort by value, so you need to use "sort" instead.