Jump to content

'.'conversation_id', 'conversations'.'conversation_subject' ' at line 2


etsted

Recommended Posts

keep getting this error: 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 '.'conversation_id', 'conversations'.'conversation_subject' ' at line 2

$sql = "SELECT                     'conversations'.'conversation_id',                    'conversations'.'conversation_subject'                    MAX('conversation_messages'.'message_date') AS 'conversation_last_reply'                    FROM 'conversations'                    LEFT JOIN 'conversation_messages' ON 'conversations'.'conversation_id' = 'conversation_messages'.'conversation_id'                    INNER JOIN 'conversation_members' ON 'conversations'.'conversation_id' = 'conversation_members'.'conversation_id'                    WHERE 'conversation_members'.'user_id' = {$_SESSION['user_id']}                    AND 'conversation_members'.'conversation_deleted' = '0'                    GROUP BY 'conversation'.'conversation_id'                    ORDER BY 'conversation_last_reply' DESC";
Link to comment
Share on other sites

I have fixed the error, but now face antoher one.

SELECT                     `conversations`.`conversation_id`,                    `conversations`.`conversation_subject`,                     MAX(`conversation_messages`.`message_date`) AS 'conversation_last_reply',                    MAX(`conversation_messages`.`message_date`) > 'conversation_last_reply'                    FROM `conversations`                    LEFT JOIN `conversation_messages` ON `conversations`.`conversation_id` = `conversation_messages`.`conversation_id`                    INNER JOIN `conversation_members` ON `conversations`.`conversation_id` = `conversation_members`.`conversation_id`                    WHERE `conversation_members`.`user_id` = {$_SESSION['user_id']}                    AND `conversation_members`.`conversation_deleted` = '0'                    GROUP BY `conversation`.`conversation_id`                    ORDER BY `conversation_last_reply` DESC

this keeps giving me an error: Unknown column 'Array' in 'where clause'

What this SQL statement is supposed to do is select all of a users messages from the database.

Ive tried to print_r $_SESSION['user_id'] and got this: Array ( [0] => 1 [user_id] => 1 )

 

im not sure why the [0] => 1 is there, but this is how i make the session.

if( ($user_id = validate_credentials($_POST['user_name'], $_POST['user_password'])) != false)        {            $_SESSION['user_id'] = $user_id;                        header("location: index2.php?page=inbox");                        die();        }

this is supposed to check if the user exist, if so then create a session.

 

this i the content of validate_credentials

function validate_credentials($user_name, $user_password){    global $con;    $user_name = mysqli_real_escape_string($con, $user_name);    $user_password = mysqli_real_escape_string($con, $user_password);        $sql = "SELECT user_id FROM users WHERE user_name='$user_name' && user_password='$user_password' ";    $query = mysqli_query($con, $sql);    $results = mysqli_fetch_array($query);        if($results == 0)    {        return false;    }        return $results;}
Link to comment
Share on other sites

Rather than...

'conversations'.'conversation_subject'

...don't you want...

conversations.conversation_subject

...or why not create an alias for the tablename?

c.conversation_subject
Link to comment
Share on other sites

conversations table:

conversation_id.

conversation_subject.

conversations is a table that creates an id foreach conversation and stores the subject of each conversation.

 

conversation_members table:

id.

conversation_id.

user_id.

conversations_last_view

conversation_deleted.

 

the conversation_id corresponds to the conversation_id inside the conversations table. user_id is the id of each user. conversations_last_view is the date of when someone last viewed the conversation. conversation_deleted tells wheter a user has deleted the conversation.

 

 

conversation_messages table:

message_id.

conversation_id.

user_id.

message_date.

message_text.

 

message_id i autoincrement. the conversation_id corresponds to the conversation_id inside the conversations table. user_id is the id of each user. message_date tellst when the last message was implemented. message_text holds the message.

Link to comment
Share on other sites

I just think you need to be careful that Php doesn't turn...

'conversations'.'conversation_subject'

...into...

conversationsconversation_subject
Link to comment
Share on other sites

// check to see if the post variables are set, then validate them if it equals to true, then log them in    if(isset($_POST['user_name']) && isset($_POST['user_password']) )    {        if( ($user_id =validate_credentials($_POST['user_name'], $_POST['user_password'])) != false)        {            die(var_dump($user_id));            $_SESSION['user_id'] = $user_id['user_id'];                        header("location: index2.php?page=inbox");                        die();        }    }    

i've tried to var_dump $user_id to se what it contains, and it contains 11 when it should contain 1, as thats what the user_id is in the DB

 

here is how the function that returns the id from the DB looks like

function validate_credentials($user_name, $user_password){    global $con;    $user_name = mysqli_real_escape_string($con, $user_name);    $user_password = mysqli_real_escape_string($con, $user_password);        $sql = "SELECT user_id FROM users WHERE user_name='$user_name' && user_password='$user_password' ";    $query = mysqli_query($con, $sql);    $results = mysqli_fetch_array($query);        if($results == 0)    {        return false;    }        return $results['user_id'];}

Now in my DB i have i have a table called "users" with 3 columns. user_id which is autoincrement, user_name which has the username, and user_password. I dont have a register form, i just make the user in phpmyadmin for testing purpose.

Edited by etsted
Link to comment
Share on other sites

so i have changed the code for testing purpose only, instead of $_SESSION['user_id'] i just added 1 instead.

this code is from the file private_message.inc.php<?php        // this will list all of the conversations to one user        function fetch_conversations_summary()        {            global $con;            $sql = "SELECT                     `conversations`.`conversation_id`,                    `conversations`.`conversation_subject`,                     MAX(`conversation_messages`.`message_date`) AS 'conversation_last_reply',                    MAX(`conversation_messages`.`message_date`) > 'conversation_last_reply'                    FROM `conversations`                    LEFT JOIN `conversation_messages` ON `conversations`.`conversation_id` = `conversation_messages`.`conversation_id`                    INNER JOIN `conversation_members` ON `conversations`.`conversation_id` = `conversation_members`.`conversation_id`                    WHERE `conversation_members`.`user_id` = 1                    AND `conversation_members`.`conversation_deleted` = '0'                    GROUP BY `conversations`.`conversation_id`                    ORDER BY `conversation_last_reply` DESC";                                $result = mysqli_query($con, $sql);                        $conversations = array();                        // store all of the id for each conversation, subject and last_reply date            while ( $row = mysqli_fetch_array($result) !== false )            {                $conversations[] = array(                    'id'        =>  $row['conversation_id'],                    'subject'   =>  $row['conversation_subject'],                    'last_reply'=>  $row['conversation_last_reply']                );            }                        return $conversations;        }?>

Every time i run this i get this error:

Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 64 bytes) in C:wampwwwcoreincprivate_message.inc.php on line 24

 

What does that mean?

Link to comment
Share on other sites

And i got a file named inbox.page.inc.php that runs the function fetch_conversations_summary()

<?php    $conversations = fetch_conversations_summary();?><div>    <a href="index2.php?page=new_conversation">new conversation</a>    <a href="index2.php?page=logout">Logout</a></div><div class="conversations"><?php    foreach ( $conversations as $conversation ){    ?>    <div class="conversation">        <h2><a href=""><?php echo $conversation['subject'];?></a></h2>        <p>Last reply: <?php echo $conversation['last_reply'];?></p>    </div><?php    }?></div>
Link to comment
Share on other sites

so i echoed $sql, and it did what i wanted it to do. I also runned the mysqli_error($con) and had no errors. Then i try to run the script but it wont work. I have also changed the script a little

"SELECT                     conversations.conversation_id, -- gets the id from conversations table                    conversations.conversation_subject, -- gets the subject from conversations table                    MAX(conversation_messages.message_date) AS 'conversation_last_reply', -- gets the latest message posted from conversation.message                    MAX(conversation_messages.message_date) > conversation_members.conversations_last_view AS 'conversation_undread' -- gets all the messages that has a "date value" greater than the users "last view" date value                    FROM conversations -- select the values from the conversations table                    LEFT JOIN conversation_messages ON conversations.conversation_id = conversation_messages.conversation_id -- also select the values from conversation_messages                    INNER JOIN conversation_members ON conversations.conversation_id = conversation_members.conversation_id -- also select the corresponding values from conversation_members                    WHERE conversation_members.user_id = 1                    AND conversation_members.conversation_deleted = '0' -- select all messages that a user hasnt deleted                    GROUP BY conversations.conversation_id                    ORDER BY conversation_last_reply DESC";
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...