Jump to content

My Php Sql statement is not working


brown2005

Recommended Posts

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 by brown2005
Link to comment
Share on other sites

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']

Link to comment
Share on other sites

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? 

Link to comment
Share on other sites

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? 
Link to comment
Share on other sites

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 by brown2005
Link to comment
Share on other sites

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 by brown2005
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

$_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

Link to comment
Share on other sites

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? 

Link to comment
Share on other sites

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 by brown2005
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.