Need Help With SQL Query

[gaza165]

I am trying to run a complex query...

 

I am basically getting private messages between users...

 

There will be the 'TO' user and the 'FROM' user... obviously i want he users to see messages that are only involved between themselves.

 

I am having problems with the query..

 

my code is below.

 

<?php
session_start();
error_reporting(E_ALL);
require_once("db_class.php");
include_once "dbconnect.php";
$db = new DB("localhost", "shoutbox", "root", "");

$touser = $_GET['to']; //user who it was sent to...
$activeuser = strtolower($_SESSION['login']['username']); //user that is logged in
$pm_pos = 0;
$pm_pos = $_SESSION['pm_position'];

if($pm_pos > 0){
$db->query("SELECT * FROM private_message WHERE message_id > :pm_pos AND (pm_to = '$touser' OR pm_to = '$user') ORDER BY message_id DESC", array("pm_pos"=> $pm_pos));
} else {
$db->query("SELECT * FROM private_message WHERE pm_to = '$user' OR pm_to = '$user'");
}

$newpmPos = $pm_pos;
while($data = $db->getAssocRow()){
if($data["message_id"] > $newpmPos) {$newpmPos = $data["message_id"];}

	$body = $data['message'];

echo "<li><h2>".$data['from_ip'].": </h2><span>".$body."</span></li>";

}

$_SESSION['pm_position'] = $newpmPos;

if($db->getLastError()){
print_r($db->getLastError());
}
?>

 

What am i doing wrong with my SQL Query??

[gaza165]

The problem is with this query!!!

 

<?
$db->query("SELECT * FROM private_message WHERE message_id > :pm_pos AND (pm_to = '$touser' OR pm_to = '$activeuser') ORDER BY message_id DESC", array("pm_pos"=> $pm_pos));
?>

[gaza165]

I really need help with this...

[gamesmstr]

SQL isn't my forte. (I use MYSQL) so I am not familiar with what you are trying to do with the array at the end, but pm_pos is a variable and so it needs the '$pm_pos' on it.  Also try adding or die (sql_error()); at the end to see what error you are getting...

 

<?
$db->query("SELECT * FROM private_message WHERE message_id > '$pm_pos' AND (pm_to = '$touser' OR pm_to = '$activeuser') ORDER BY message_id DESC", array("pm_pos"=> $pm_pos));
?>

 

[gaza165]

No that bit is correct its the bit after that

 

I only want to bring back the records that are anything to do with either user...

 

So the user sending the message will see what they have written to another and also see the posts that are being sent to them.

 

 

[kickstart]

Hi

 

You are using a class for accessing MySQL. Where have you got that class from? I presume it is trying to substitute in variables, looking for : markers.

 

Also I presume first time in that the 2nd query is executed. Trouble is with no order by clause it could land up bringing back the last record and so $_SESSION['pm_position'] is the last record.

 

All the best

 

Keith