trying to install this script http://www.9lessons.info/2013/05/message-conversation-database-design.html
ive created the database but i do not understand the php code. How does it connect to the database and how do i seperate it to categories such as inbox,messages,sent. any help be appriciated.
DJ, long time to write. I have been off the site for quite some time and am now back. Let’s look at this project further for you…
Well, the site has just about everything you need as a base for your system.
First, it explains how to create each of the tables needed.
Next, it explains each of the database queries that you need to use to load various parts of the data content that is stored in the tables.
Lastly, it shows sample code on how to create a new conversation, how to read it back out and how to handle replies to each conversation.
So, all of the information is there. Which are you having trouble with? Let’s step thru it a bit…
There are three tables. You said you created them all. Correct?
Next, review all of the boxes with queries inside them. Copy them into a list so you have them as needed. They each show what you would need for the database query for that item. Since the entire system creates new conversations, allows you to display them and handles all replies, it appears that everything you need is there for queries.
The PHP code there is customized as a sample of how you would handle various parts of your system. This is where you, yourself would alter the code to fit your application. So, you would need a form or way for the user to create a new conversation and an area for them to reply to a conversation.
The conversations would have to be displayed in some sort of a list and have a reply button or delete button or other options. Then, those would be linked to the sample code with your customizations.
So, lay it out some and create your sample code and let us know where you are stuck. I am sure we can help you out… Good luck…
Inbox.php
[php]include ‘config.php’
<?php $query= mysql_query("SELECT u.user_id,c.c_id,u.username,u.email FROM conversation c, users u WHERE CASE WHEN c.user_one = '$user_one' THEN c.user_two = u.user_id WHEN u.user_two = '$user_one' THEN c.user_one= u.user_id END AND ( c.user_one ='$user_one' OR c.user_two ='$user_one' ) Order by c.c_id DESC Limit 20") or die(mysql_error()); while($row=mysql_fetch_array($query)) { $c_id=$row['cid']; $user_id=$row['user_id']; $username=$row['username']; $email=$row['email']; $cquery= mysql_query("SELECT R.cr_id,R.time,R.reply FROM conversation_reply R WHERE R.c_id_fk='$c_id' ORDER BY R.cr_id DESC LIMIT 1") or die(mysql_error()); $crow=mysql_fetch_array($cquery); $cr_id=$crow['cr_id']; $reply=$crow['reply']; $time=$crow['time']; //HTML Output. } ?>[/php]
sendpm.php
[php]<?php
$query= mysql_query(“SELECT R.cr_id,R.time,R.reply,U.user_id,U.username,U.email FROM users U, conversation_reply R WHERE R.user_id_fk=U.user_id and R.c_id_fk=’$c_id’ ORDER BY R.cr_id ASC LIMIT 20”) or die(mysql_error());
while($row=mysql_fetch_array($query))
{
$cr_id=$row[‘cr_id’];
$time=$row[‘time’];
$reply=$row[‘reply’];
$user_id=$row[‘user_id’];
$username=$row[‘username’];
$email=$row[‘email’];
//HTML Output
}
?>[/php]
sent.php
[php]<?php
$user_one=mysql_real_escape_string($_GET[‘user_session’]);
$user_two=mysql_real_escape_string($_GET[‘user_two’]);
if($user_one!=$user_two)
{
$q= mysql_query("SELECT c_id FROM conversation WHERE (user_one=’$user_one’ and user_two=’$user_two’) or (user_one=’$user_two’ and user_two=’$user_one’) ") or die(mysql_error());
$time=time();
$ip=$_SERVER[‘REMOTE_ADDR’];
if(mysql_num_rows($q)==0)
{
$query = mysql_query(“INSERT INTO conversation (user_one,user_two,ip,time) VALUES (’$user_one’,’$user_two’,’$ip’,’$time’)”) or die(mysql_error());
$q=mysql_query(“SELECT c_id FROM conversation WHERE user_one=’$user_one’ ORDER BY c_id DESC limit 1”);
$v=mysql_fetch_array($q);
return $v[‘c_id’];
}
else
{
$v=mysql_fetch_array($q);
return $v[‘c_id’];
}
}
?>
reply.php
[php]<?php
$reply=mysql_real_escape_string($_POST[‘reply’]);
$cid=mysql_real_escape_string($_POST[‘cid’]);
$uid=mysql_real_escape_string($uid_session);
$time=time();
$ip=$_SERVER[‘REMOTE_ADDR’];
$q= mysql_query(“INSERT INTO conversation_reply (user_id_fk,reply,ip,time,c_id_fk) VALUES (’$uid’,’$reply’,’$ip’,’$time’,’$cid’)”) or die(mysql_error());
?>[/php]
Okay, you have three groups of code ready. What about your forms for entering a message?
Are they going to be part of pages you already have in place? You might want to just work
on simple pages for now for testing. You would need a form and the fields the inputs.
Also, you need to create the list of messages from the “inbox” code to display the message list.
Have you decided how the list of messages will look yet? I mean, are you just going to list them as links or in a table with buttons for reading or replying? Do you have a site that you know already that has a message list similar to what you want to do? Sometimes it is easy to mimic a layout that already works.
Just ideas to continue with… What can we help you with next?
Oooops, forgot, it was FaceBook type of display you were after…
I just remembered. So, did you get the form for writing figured out yet? You can actually look at FB’s site and see how it is laid out, then, create your version. Don’t worry about the CSS at this point, just the field set up where you would have a field to response with your reply. The outputs of the database would be in DIV’s I would guess one after another so the flow in order… Hope that makes sense…
ive not got any for, figured out at all yet
would this be a good start
[php]
WallScript Version 6.0 <div id='main_left'>
<div class="some-content-related-div">
<div id="replylist_content" class="conversation_grid">
</div>
</div>
</div>
No conversation selected.
In general, yes. Do you have it online yet? I mean, are you testing on a server or a local server?
For me to test it I would have to load it to a server. I actually have someone else looking at this same type of code. I thought it was you for a moment. Anyways, to test it, start with just getting it to load messages to the database. Once that is done, then work on displaying them back. And, finally work on the reply sections. In that manner, you won’t be cramped up working on all three at once. First, get it to accept inputs and store it into the database.
Now, you showed code that near the end had DIV’s for displaying things, but, no FORM inputs for Fields that will allow the user to type in the text. So, you need create a way for the system to read the text that is the message. Hope that makes sense. Text inputs can not be just DIV’s. They need to be an input field, which means you set up a and post the data to your save-in-database routines…
its the script to test or the php file that i need help creating. i got the database on mysql created but i dont know how to create a php script to test it.
Well, first, what you have shown is mostly Javascript. This is used to manipulate data and items already in a browser. So, that has little to do with forms and using data in a database.
Have you ever worked with forms? Do you know how to save data from a form into a database table?
Here is basically how it would all be handled. (Just in general terms, not detailed for your use.)
First, you have to have a plain HTML page that lays out a form. A form would have the fields that would be text input fields where the user could type in their message. There would either be a button that allows them to submit the text they wrote or you can use the enter key to do this, but, it is trickier to do that way. Usually, there is a small “submit” or “send” button. The button would submit the form to either the same page or a second page that contains the code for saving the text to the database.
Next, there would be the PHP code which would accept the posted text-input field and add it to the message database tables with the user’s ID code.
And, another form would be created that pulls out the saved messages and displays them using PHP to query the database and pull out the needed data.
So, that is basically the three main steps that need to be used for your project. To accomplish this, you already have all the PHP code. You have the database to store and retrieve the data. (In this case, the messages.) You also have a lot of Javascript code that manipulates the data on the page. But, you need to decide how this is going to be used. Then, create the page that displays it.
To decide the use of it, you need to first decide how it is displayed. So, in FaceBook’s page, you see messages listed one after another. But, if someone enters a message at the same time you are viewing your page, you do NOT see the other person’s message. You need to refresh your page to see messages entered after you viewed your page. Hope that makes sense. So, there are ways to refresh the page or part of the part using Javascript, but, we can talk about that later on.
My suggestion would be to not make it “dynamic” for now, but, to just get it to show the entries and worry about tricky code later on.
So, now we have a game plan. First, make a simple HTML page with a form on it. some field or fields, a submit button etc… If you need help on that, here is a simple tutorial on forms:
http://www.w3schools.com/php/php_form_complete.asp
On that page, you can use the links on the left side or the PREVIOUS/NEXT chapters to view other form info…
Once you create the form and layout of the messages, you have names for the text field to enter messages in and the submit button name. Then, you can use your PHP save-to-database code that you got from the samples and change the names to match your field names.
Next, you need to test this part and make sure it is saving the messages into the database. You would have to log into your MySQL-manager page and view the live data in the database table to make sure you are saving the data in it.
Once you have the data saving to the database, then, we can work on getting the messages back out and displayed into the page above the line that lets you submit new messages.
Hope all that makes sense. First design your plan for layout of the page, then create the forms and lastly connect the PHP code to the form page. Good luck…
ive done contact forms before.
input name,email,number,comments.
Well, then get one online so we can see it and we can help you link it to your database if you can not figure it out.
i got this but on the site i get the error Access denied for user ‘’@‘10.0.230.13’ (using password: NO)
[php]<?php
$query= mysql_query(“SELECT u.user_id,c.c_id,u.username,u.email
FROM conversation c, users u
WHERE CASE
WHEN c.user_one = ‘$user_one’
THEN c.user_two = u.user_id
WHEN u.user_two = ‘$user_one’
THEN c.user_one= u.user_id
END
AND (
c.user_one =’$user_one’
OR c.user_two =’$user_one’
)
Order by c.c_id DESC Limit 20”) or die(mysql_error());
while($row=mysql_fetch_array($query))
{
$c_id=$row[‘cid’];
$user_id=$row[‘user_id’];
$username=$row[‘username’];
$email=$row[‘email’];
$cquery= mysql_query(“SELECT R.cr_id,R.time,R.reply FROM conversation_reply R WHERE R.c_id_fk=’$c_id’ ORDER BY R.cr_id DESC LIMIT 1”) or die(mysql_error());
$crow=mysql_fetch_array($cquery);
$cr_id=$crow[‘cr_id’];
$reply=$crow[‘reply’];
$time=$crow[‘time’];
//HTML Output.
}
?>[/php]
this code does the same
[php]<?php
$reply=mysql_real_escape_string($_POST[‘reply’]);
$cid=mysql_real_escape_string($_POST[‘cid’]);
$uid=mysql_real_escape_string($uid_session);
$time=time();
$ip=$_SERVER[‘REMOTE_ADDR’];
$q= mysql_query(“INSERT INTO conversation_reply (user_id_fk,reply,ip,time,c_id_fk) VALUES (’$uid’,’$reply’,’$ip’,’$time’,’$cid’)”) or die(mysql_error());
?>[/php]
Did you set up the database connection first?
Your code starts at line #2 with a query, but, no connection was made before that line.
You have to connect to your database with user info and password first.
(Unless you just are not showing it to us. Which of course, you never show the password online!)
this still does not work
[php]
<?php include 'config.php'; $user_one=mysql_real_escape_string($_GET['user_session']); $user_two=mysql_real_escape_string($_GET['user_two']); if($user_one!=$user_two) { $q= mysql_query("SELECT c_id FROM conversation WHERE (user_one='$user_one' and user_two='$user_two') or (user_one='$user_two' and user_two='$user_one') ") or die(mysql_error()); $time=time(); $ip=$_SERVER['REMOTE_ADDR']; if(mysql_num_rows($q)==0) { $query = mysql_query("INSERT INTO conversation (user_one,user_two,ip,time) VALUES ('$user_one','$user_two','$ip','$time')") or die(mysql_error()); $q=mysql_query("SELECT c_id FROM conversation WHERE user_one='$user_one' ORDER BY c_id DESC limit 1"); $v=mysql_fetch_array($q); return $v['c_id']; } else { $v=mysql_fetch_array($q); return $v['c_id']; } } ?>[/php]Okay, perhaps I am not understanding correctly.
On EVERY page that has a query, you have to already have the connection to the database created.
So, whenever you use "mysql_query(“some query”); in a page, before that can be used, you must
have your connection made to the database. Most people place it inside the config.php file.
I assume that inside your config.php file, you have the connection to the database and you have
the commands to select the database table to use.
And, why does it not work? I mean what error do you get? Have you debugged it to see where it dies?
In case you are not clear on debugging, a simple way is to just throw in a “DIE” command that shows the
values that you have a one point and see if they are valid. So, you could just use something like this line:
die(“got here…”);
And, place it at line #9 to see if your code gets there or not. If it does, move it down to line 17 and see
if it gets there, etc. In this manner, you can see where it is dying…
Oh, one other thing, you show the code as a file or program, but, you are using a “RETURN” in it like
it was a function. So, I am not really clear on what this code does. It appears to be a routine to load a message or create a new one if one does not exist. But, then it returns, so it goes nowhere.
Hmmm, maybe you are confused on how PHP works. You create a HTML file which contains the form and fields for data that the user will enter. Then, when they press the SUBMIT button the form sends that data to the file named in the tag for action. That file is usually the PHP file that the form sends data to. Then, the PHP file takes the data and processes it and updates the database and then sends the user back to either the same page with updated displays or to a new page with more info and forms. So, for a chat page, all of the PHP and the forms may be on one page. It can just post to itself. But, you are showing a PHP routine that looks like it is by itself. Not sure where it is going when done processing…
Did this help or make if more confusing?