[Resolved] Reading info from mySQL database at specific ID

wasson

I'm just getting my feet wet with the world of PHP and mySQL, and I'm learning it by hacking up other people's code and using it on my own site. For my second little project, I wanted to add commenting ability to my moblog script that I downloaded from someone else.

I inserted row into the current moblog mySQL table called "comment", and within phpMyAdmin I added a comment manually. I just wanted to see if I could get it to display on the moblog on the proper page before I worried about adding an actual commenting field. I have failed miserably at this task. Below is the code that seems to get me the closest results:

mysql_connect($db["addr"],$db["user"],$db["pass"]);

$result = mysql_db_query($db["use"], "SELECT * from $table order by id desc") or die (mysql_error());
while ($qry = mysql_fetch_array($result)) {
echo "<li>$qry[comment]</li>";
}

Basically, this code cycles through every "comment' filed in my mySQL table and displays them. Almost all of them are empty, except for the one I added myself. It kind of works, except that on every single page, every single comment would be shown.

How do I code it so that only the comment only shows for the specific ID I want it to show for?

Also, am I doing this the best way? Can I insert more than 1 comment into the same row of a database and display all of them? I've read books and some tutorials online and none seem to answer this incredibly basic stuff. I don't even know what to look for to find my answer without "personal" assisstance.

Thanks in advice.

bpat1434

Since you want to learn (Kudos to you), check out: Group By (mySQL) and run a second query looking for all comments that have the id of the current post.

Something like: This is pseudo code. It's just a framework to help you. NOT COMPLETE/WORKING.

<?php
$img_id = $_GET['img'];

mysql_connect($db["addr"],$db["user"],$db["pass"]);

$result = mysql_db_query($db["use"], "SELECT * FROM $table WHERE pid = '$img_id' ORDER BY id ASC") or die (mysql_error());
while($row = mysql_fetch_array($result))
{
    echo "<li>".$com['comment']."</li>";
}

?>

Hope that helps.

~Brett

The_Chancer

Have a look at the SELECT syntax page on the mysql pages...

I think you might be looking for something like

SELECT * FROM dbase WHERE column='var'

This will select all rows with the value of var in the column called column

You can also use AND column2='var2' AND column3='var3' etc... to minimise the result set produced by the query.

wasson

Originally posted by bpat1434
Since you want to learn (Kudos to you), check out: Group By (mySQL) and run a second query looking for all comments that have the id of the current post.

That definitely helped. I knew I had to somehow group the entries by id, then get the comments for that id.

I used your code and its returning the same results as the code I used before. I'm sure I'm just missing something simple, but I'm not able to work out the problem in my head for some reason. If you look at my moblog, you can see the result I'm getting:

http://www.tim.cx/moblog/

Click on any of the images and you'll get the same result... a long line of empty <li>'s with the 1 comment I manually typed in at the bottom. Obviously the code is just reading every comment in the table instead of just that 1 row, even with your code. Your code remained unaltered, except I changed "$results" to "$result". Is there something else in your code I should have changed to make it specific to my table (like I said, I'm stupidly new at this stuff)?

The_Chancer

Nice 404 page you have created there 😃 Guess it is not on the server at the moment......

wasson

Ah crap, let's try this again...

http://www.tim.cx/mob/

bpat1434

Try not modifying my code. Leave it the way it is. I know it will work if you don't alter it, as I use it as well.

For a more intricate example, go here:

My Intricate Example

Look at the drop-down menus. You'll notice how I group them together. I start off with the year (2004, 2005), then group them by year and display the categories, then from that, split the categories up into events.

Three different queries. HAH!

Oh, and here's some back-end code for that:

$yquery = "SELECT * FROM `Years` ORDER BY yid";
$yresult = mysql_query($yquery);
while($y = mysql_fetch_array($yresult)){
	$year_select .= "<option value='".$y['yid']."'>".$y['Year']."</option>";
}

$cquery = "SELECT * FROM `Years` ORDER BY yid DESC";
$cresult = mysql_query($cquery);
while($c = mysql_fetch_array($cresult)){
	$cat_select .= "<optgroup label='".$c['Year']."'>";
	$selsql = mysql_query("SELECT * FROM `Categories` WHERE cyid = '".$c['yid']."' ORDER BY cid ASC");
	while($_row = mysql_fetch_array($selsql)){
		$cat_select .= "<option value='".$_row['cid']."'>".$_row['Category']."</option>";
	}
	$cat_select .= "</optgroup>";
}

$equery = "SELECT * FROM `Categories` ORDER BY cid DESC";
$eresult = mysql_query($equery);
while($e = mysql_fetch_array($eresult)){
	$evt_select .= "<optgroup label='".$e['Category']."'>";
	$selsql = mysql_query("SELECT * FROM Years AS t1, Events AS t2 WHERE t2.ecid = '".$e['cid']."' AND t1.yid = ".$e['cyid']." ORDER BY t2.Event ASC");
	while($_e = mysql_fetch_array($selsql)){
		$evt_select .= "<option value='".$_e['eid']."'>[".$_e['Year']."] ".$_e['Event']."</option>";
	}
	$evt_select .= "</optgroup>";
}

Well, maybe I will modify my code in my previous post. Check that, and try it again.

You were right with it returning all items in the database. I didn't limit the query. It wasn't until I saw your URL that I was able to limit the query. But now, my code should work.

~Brett

wasson

I've now implemented your code... I'm getting another error. I'm guessing this is because "img" isn't the correct code to use in this line:

$img_id = $_GET['img'];

Just speculation, of course. just for my own curiousity, is $_GET['img'] just getting the "?img=22" out of the address bar? Or is it accessing the database?

Anyway, the new error message is now visible on the site. Again, any help is greatly appreciated.

bpat1434

The code $_GET['img'] is getting the image number out of the database. I'm guessing that the image corresponds to something in the database (dear god I hope so). If not, then you need to add a variable to the URL (like id=##) so that you can limit what is returned.

~Brett

wasson

There's no entry in the database called "img", although from what I can tell, the numbers correspond to the "id" row (or is it column?) in the database.

So... can I make a new row (or is it column) in the database called "img" that simply shadows what's in the "id" field (a sloppy solution, I know...), or do I need to figure out a way to put "?id=" in the URL?

bpat1434

If the image number corresponds to the id number in the row, change the code to id = $_GET['img']

<?php
$img_id = $_GET['img'];

mysql_connect($db["addr"],$db["user"],$db["pass"]);

$result = mysql_db_query($db["use"], "SELECT * FROM $table WHERE id = '$img_id' ORDER BY id ASC") or die (mysql_error());
while($row = mysql_fetch_array($result))
{
    echo "<li>".$com['comment']."</li>";
}

?>

Think about what you were saying to me. I don't for a second believe that you needed me to tell you how to fix that. You really answered your own question, in your question.

the numbers correspond to the "id"

Doh!

We all have those moments.

~Brett

wasson

Honestly, I'm just not good enough at PHP/mySQL to know that. It wasn't meant to be a stupid question.

Well, it looks like that half-worked. I now get an empty bullet on each of the pages, which means it's not finding a comment and stopping. However, it's also not displaying the comments I added manually within phpMyAdmin. I don't even know where to begin to solve this problem. Any ideas?

bpat1434

I'd have to see your entire code, and your table setup to even begin.

~Brett

wasson

HOLY CRAP. I can't believe it, but somehow I managed to make it work. Here's the code that did it:

//display comments

$img_id = $_GET['img'];

mysql_connect($db["addr"],$db["user"],$db["pass"]);

$result = mysql_db_query($db["use"], "SELECT * FROM $table WHERE id = '$img_id' ORDER BY id ASC") or die (mysql_error());

if (mysql_num_rows($result)) {
while($qry = mysql_fetch_array($result))
{
    echo "<li>".$qry['comment']."</li>";
}
}

Now onto the code used to ADD the comments to the database without me having to do it manually.

First, can I bother you with some newbie questions?

For comments, I want to collect the commentor's name and email address as well as date/time they posted. Will I need a table row for name, email address, etc?

Also, can more than one comment be inserted into the same row on the table? Do I need to do anything special so the data won't be overwritten?

And finally, are there any tricks to keeping the data "together"? That is... keeping the commentor's name with the comment they made? Is it easier than my brain is making it out to be?

EDIT- in my excitement that I got it working and so eager to bother you with more questions, I forgot to thank you for your help. I definitely wouldn't have gotten it sorted out without your help. Thanks.

bpat1434

You would have to add a few columns to the database: email, name, date.

Then, use a basic HTML form that posts to a php script that grabs the variables, and inserts them into the database. You'd also need to pass the picure/row ID with the form.

Personally, I'd use the following table layout:

id - int(11), NOT NULL, auto_increment, Primary Key
photo - int(11), NOT NULL, ,
name - text, NOT NULL
email - text, NOT NULL
comments - text, NOT NULL
date - date/time

Then, the id would be a unique identified for each row, and the photo column would correspond to the current photo being viewed.

Here's some mock-up code that I can give you in less than 5 minutes (assuming you're using the database strucutre above):

<?php
if(empty($_POST['pic_id']))
{
?>
<html>
<head>
  <title>Photo Comments</title>
</head>
<body>
  <form action="comment.php" method="POST">
    <input type="hidden" name="pic_id" value="<?= $_GET['img'] ?>">
    <b>Your Name:</b> <input type="text" name="name"><br>
    <b>Your Email:</b> <input type="text" name="email"><br>
    <b>Comments:</b><br>
    <textarea name="comments" cols="35" rows="15"></textarea>
    <input type="hidden" name="date" value="<?= date("r") ?>">
    <input type="submit" value="Add Comment">
  </form>
</body>
</html>
<?
}
else
{
    $name = $_REQUEST['name'];
    $email = $_REQUEST['email'];
    $date = $_REQUEST['date'];
    $comments = $_REQUEST['comments'];
    $pic_id = $_REQUEST['pic_id'];

$sql = "INSERT INTO `table_name` (photo, name, email, comments, date) VALUES ('$pic_id', '$name', '$email', '$comments', '$date')";
$result = mysql_query($sql);
if(!$result)
{
    echo "mySQL Error: ".mysql_error();
}
else
{
    echo "Comment added!";
    echo "<a href='".$_SERVER['HTTP_HOST']."/photo.php?img=".$pic_id."'>Click here to return</a>";
}
}
?>

~Brett

wasson

Thanks for the reply, Brett, but... I'm embarrassed to say that it didn't work. I'm trying my damndest not to just copy and paste the code you give me so that I can learn a little bit in the process making this work.

Anyway, in my efforts to make this simple for me to pick up on, I'm ignoring my future plans to log the name, email address, etc. I'm trying to just make a simple form that will insert the comment for the appropriate picture. And still, I can't do it.

I can make it insert a new row with the comment no problem. But if you go back to the site now and try to add a comment, you get this error:

mySQL Error: Duplicate entry '9' for key 1

I'm guessing that its not inserting the data because the row is already labeled '9', and it won't overwrite the data. Is there a mySQL command for "add the data at this row" or something similar?

Also, I don't quite understand the point of the "photo" mySQL column you suggested above. Is that necessary to make the whole thing work?

bpat1434

Originally posted by bpat1434
Personally, I'd use the following table layout:

id - int(11), NOT NULL, auto_increment, Primary Key
photo - int(11), NOT NULL, ,
name - text, NOT NULL
email - text, NOT NULL
comments - text, NOT NULL
date - date/time

id
This is an identifier to the table. It has nothing to do other than give a unique number to the database to use as a key.

photo
An integer that corresponds to the img value passed through the URL. This will be so that multiple comments can be used with the same photo, but each row will have its own unique identifier (the id column).

name
The name of the commenter

email
The email of the commenter

comments
The comments of the commenter

date
The date and time (RFC formatted) of the comment post. Helpful for sorting by most recent post(s).

Not sure what's going on without seeing the code that you're using. From now on, if you post an error, post some code with it. Like your SQL query code would be helpful for this problem. I can't help you until I see what you're trying to do.

~Brett

wasson

Okay, that clear things up a lot. So, there's no way to bundle all the comments at the same id? So each comment has a different id, but it also has a "photo" identifier, so that the mySQL query can find all comments with the "photo" identifier and display them? Am I on the right track?

If this is the case, would it just be easier to create a new table called "moblogcomments" and store everything in there? wouldn't that make everything easier to keep track of for a newbie like me?

bpat1434

You can do it how you wish. I was figuring that you would already have a table named "moblogcomments" which is seperate from a table that housed what image number corresponded to where.

You are on the right track. Here's an example:

moblogphotos Table

| id |                photo                 |
| 1  | /images/photos/0001.jpg |
| 2  | /images/photos/0002.jpg |
|10 | /images/photos/0010.jpg |

moblogcomments Table

| id | photo |       Comments        |
| 1  |     1    | This is a comment   |
| 2  |     1    | Another comment, for same picture |
| 3  |     2    | Comment for photo 2 |
| 4  |    10   | Comment for photo 10 |

So the column in moblogcomments corresponds to the id of moblogphotos. Simple isn't it?

When you do a query for comments, mySQL will look in table moblogcomments for any rows that have a img_num in the column photo, where img_num is the corresponding ID to the photo.

~Brett

wasson

You are totally the man. I can't thank you enough for walking me through the process. Once I got it in my thick skull to just create a new table, it all fell together relatively quickly.

My original intent was to do my table like this:

| id | photo |       Comments        |
| 1  |     1    | 1. This is a comment   |
|     |           | 2. This is another comment for the sam picture |
| 2  |     2    | A comment for photo two. |

That would be a lot more complicated, of course. But is it even possible? Can 2 comments be stored in the same "cell" and be separated by the PHP?

Either way, the comments are working. Thanks a lot for all your help. I very much appreciate it. I'm going to work on adding a name/email address field now. It shouldn't be hard now that I've got the basics down.