etsted Posted May 11, 2014 Share Posted May 11, 2014 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 More sharing options...
etsted Posted May 11, 2014 Author Share Posted May 11, 2014 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 More sharing options...
davej Posted May 11, 2014 Share Posted May 11, 2014 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 More sharing options...
etsted Posted May 11, 2014 Author Share Posted May 11, 2014 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 More sharing options...
davej Posted May 11, 2014 Share Posted May 11, 2014 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 More sharing options...
etsted Posted May 11, 2014 Author Share Posted May 11, 2014 But any help on my problem? Link to comment Share on other sites More sharing options...
etsted Posted May 11, 2014 Author Share Posted May 11, 2014 I just think you need to be careful that Php doesn't turn... 'conversations'.'conversation_subject' ...into... conversationsconversation_subject I didnt think so either Link to comment Share on other sites More sharing options...
davej Posted May 11, 2014 Share Posted May 11, 2014 Are you testing this query in phpMyAdmin or MySQL Workbench or in a test Php file? Link to comment Share on other sites More sharing options...
etsted Posted May 11, 2014 Author Share Posted May 11, 2014 why is $_SESSION['user_id'] an array? Link to comment Share on other sites More sharing options...
etsted Posted May 11, 2014 Author Share Posted May 11, 2014 test php file Link to comment Share on other sites More sharing options...
etsted Posted May 11, 2014 Author Share Posted May 11, 2014 the problem is that $_SESSION['user_id'] somehow returns an array Link to comment Share on other sites More sharing options...
davej Posted May 11, 2014 Share Posted May 11, 2014 var_dump( $_SESSION['user_id'] ); Link to comment Share on other sites More sharing options...
etsted Posted May 11, 2014 Author Share Posted May 11, 2014 array (size=2)0 => string '1' (length=1)'user_id' => string '1' (length=1) Link to comment Share on other sites More sharing options...
etsted Posted May 11, 2014 Author Share Posted May 11, 2014 I also tried var_dump($_SESSION); array (size=1) 'user_id' => array (size=2) 0 => string '1' (length=1) 'user_id' => string '1' (length=1) Link to comment Share on other sites More sharing options...
etsted Posted May 11, 2014 Author Share Posted May 11, 2014 (edited) // 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 May 11, 2014 by etsted Link to comment Share on other sites More sharing options...
etsted Posted May 11, 2014 Author Share Posted May 11, 2014 okay so now i have test var_dump($_SESSION['user_id'] and it returned 1, then i tried print_r($_SESSION['user_id']) and it returned 11. Link to comment Share on other sites More sharing options...
davej Posted May 11, 2014 Share Posted May 11, 2014 This looks illogical... $_SESSION['user_id'] = $user_id['user_id']; Link to comment Share on other sites More sharing options...
etsted Posted May 11, 2014 Author Share Posted May 11, 2014 This looks illogical... $_SESSION['user_id'] = $user_id['user_id']; Yeah i changed that but still didnt work Link to comment Share on other sites More sharing options...
davej Posted May 11, 2014 Share Posted May 11, 2014 So it is maybe... $_SESSION['user_id'] = $user['user_id']; Link to comment Share on other sites More sharing options...
etsted Posted May 11, 2014 Author Share Posted May 11, 2014 (edited) So it is maybe... $_SESSION['user_id'] = $user_id; i changed it to this, then i used print_r, but it still returned 11(it should return 1, as in the DB). Edited May 11, 2014 by etsted Link to comment Share on other sites More sharing options...
etsted Posted May 11, 2014 Author Share Posted May 11, 2014 So it is maybe... $_SESSION['user_id'] = $user['user_id']; what do u mean? i dont have a variable named $user. Link to comment Share on other sites More sharing options...
etsted Posted May 11, 2014 Author Share Posted May 11, 2014 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 More sharing options...
etsted Posted May 11, 2014 Author Share Posted May 11, 2014 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 More sharing options...
davej Posted May 11, 2014 Share Posted May 11, 2014 Try... echo $sql; ...and make sure it says what you expect it to say. Link to comment Share on other sites More sharing options...
etsted Posted May 11, 2014 Author Share Posted May 11, 2014 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 More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now