Table/query confusion

[Elodin]

Hello,

I’m trying to set up two tables in a database and then make two queries to the posts table. The first query is supposed to return the first five blog posts, and the second is supposed to return all the comments for the blog posts where the user id equals 1.

[php]

<?php include("dbconnect.php"); // using dbconnect object $con = new dbconnect(); $con->connect(); mysql_query("CREATE table posts ( post_id INT NOT NULL AUTO_INCREMENT Primary Key, author VARCHAR(32) NOT NULL, title VARCHAR(32) NOT NULL, content VARCHAR(32) NOT NULL, date_published TIMESTAMP(8))") or die(mysql_error()); mysql_query("CREATE table comments (comment_id INT NOT NULL AUTO_INCREMENT Primary Key, post_id INT NOT NULL, name VARCHAR(32) NOT NULL, email VARCHAR(32) NOT NULL, website VARCHAR(32) NOT NULL, content TEXT NOT NULL, date_published TIMESTAMP(8))") or die(mysql_error()); mysql_query("INSERT INTO posts (post_id, author, title, content) VALUES(1, 'John', 'First post', 'Lorem ipsum dolor sit amet'), (2, 'Tom', 'Second post', 'Sed ullamcorper consectetur ligula'), (3, 'Jane', 'Third post', 'Art'), (4, 'Paul', 'Fourth post', 'Sed ullamcorper consectetur ligula'), (5, 'clark', 'Fifth post', 'Aliquam velit nunc, accumsan id semper a'), (6, 'Mary', 'Sixth post', 'habitant morbi tristique senectus ') or die(mysql_error()"); mysql_query("INSERT INTO comments (comment_id, post_id, name, email, website, content) VALUES(1, 3, 'Tom', '[email protected]', 'www.google.com', 'Pellentesque habitant morbi tristique senectus'), (2, 1, 'Peter', '[email protected]', 'www.yahoo.com', 'convallis et ipsum. Integer'), (3, 5, 'Amanda', '[email protected]', 'www.metacritic.com', 'accumsan id semper a,'), (4, 1, 'Clark', '[email protected]', 'www.asu.edu', 'ipsum neque et nulla'), (5, 4, 'Paul', '[email protected]', 'www.firefox.com', 'Duis elit nisl, laoreet at fringilla'), (6, 2, 'Steve', '[email protected]', 'www.kudzu.com', 'a, convallis et ipsum. ') or die(mysql_error()"); $result = mysql_query("SELECT post_id, author, content FROM posts WHERE id<6"); print(""); print(""); print(""); print(""); print(""); print(""); print(""); print(""); while($row = mysql_fetch_array($result)) { print(""); echo " "; print(""); } print("

Id	Author	Title	Content
" . $row['id'] . "	" . $row['author'] . "	" . $row['title'] . "	" . $row['content'] . "

"); print(""); $result2 = mysql_query("SELECT content FROM comments WHERE post_id = 1"); while($row = mysql_fetch_array($result2)) { echo $row['comment']; echo "
"; } mysql_close($con); ?>

[/php]

The dbconnect class contains:

[php]<?php
class dbconnect{
function connect()
{
$con=mysql_connect(“localhost”,“username",“password");
if (!$con)
{ die('Could not connect: ’ . mysql_error()); }
mysql_select_db(“username”, $con); // your phpMySQL user name
}
} ?> [/php]

This was a homework assignment for a course, but I already turned it in and lost points since it didn’t work. I’m trying to figure out what I did wrong for my next assignment. All I get when I try to access the page with this script is the phrase “table already created”, instead of any query results. Any help would be greatly appreciated.

[richei]

There’s 2 ways that i can see of to go about this. The first way (and probably the simplest) would be do a direct join, my terminology for doing a join without using JOIN)
[php]
$result = mysql_query(“SELECT * FROM posts, comments WHERE post_id < 6”) or die(mysql_error());

echo "

";
while($row = mysql_fetch_array($result)) {
echo "







";
}
echo "

Post ID	Author	Title	Content
$row[post_id]	$row[author]	$row[title]	$row[content]
$row[comment’]

"; [/php]

In this instance, you’re doing 1 query for all the information contained in the database. You can be selective like you had it, but i didn’t know what to select. Just put your stuff back in in there. Also fixed your html. My echo’s look wrong, but it is proper syntex.

The other way of doing it would be to use an actual JOIN ON, just have to be careful which one you use because they’ll yield different results. There’s plenty of tutorials on that, so you can look that up

[Elodin]

Thank you for the response. I made the changes to my code and it looks a lot better now. Unfortunately, I’m still getting the message “Table ‘posts’ already exists” and nothing else when I visit the php site. Ideally I want the information in the tables that I selected in the queries to show up.

Current code:

[php]<?php

include(“dbconnect.php”); // using dbconnect object
$con = new dbconnect();
$con->connect();
mysql_query(“CREATE table posts
( post_id INT NOT NULL AUTO_INCREMENT Primary Key,
author VARCHAR(32) NOT NULL,
title VARCHAR(32) NOT NULL,
content VARCHAR(32) NOT NULL,
date_published TIMESTAMP(8))”)
or die(mysql_error());

mysql_query(“CREATE table comments
(comment_id INT NOT NULL AUTO_INCREMENT Primary Key,
post_id INT NOT NULL,
name VARCHAR(32) NOT NULL,
email VARCHAR(32) NOT NULL,
website VARCHAR(32) NOT NULL,
content TEXT NOT NULL,
date_published TIMESTAMP(8))”)
or die(mysql_error());

mysql_query(“INSERT INTO posts
(post_id, author, title, content)
VALUES(1, ‘John’, ‘First post’, ‘Lorem ipsum dolor sit amet’),
(2, ‘Tom’, ‘Second post’, ‘Sed ullamcorper consectetur ligula’),
(3, ‘Jane’, ‘Third post’, ‘Art’),
(4, ‘Paul’, ‘Fourth post’, ‘Sed ullamcorper consectetur ligula’),
(5, ‘clark’, ‘Fifth post’, ‘Aliquam velit nunc, accumsan id semper a’),
(6, ‘Mary’, ‘Sixth post’, 'habitant morbi tristique senectus ')
or die(mysql_error()”);

mysql_query(“INSERT INTO comments
(comment_id, post_id, name, email, website, content)
VALUES(1, 3, ‘Tom’, ‘[email protected]’, ‘www.google.com’, ‘Pellentesque habitant morbi tristique senectus’),
(2, 1, ‘Peter’, ‘[email protected]’, ‘www.yahoo.com’, ‘convallis et ipsum. Integer’),
(3, 5, ‘Amanda’, ‘[email protected]’, ‘www.metacritic.com’, ‘accumsan id semper a,’),
(4, 1, ‘Clark’, ‘[email protected]’, ‘www.asu.edu’, ‘ipsum neque et nulla’),
(5, 4, ‘Paul’, ‘[email protected]’, ‘www.firefox.com’, ‘Duis elit nisl, laoreet at fringilla’),
(6, 2, ‘Steve’, ‘[email protected]’, ‘www.kudzu.com’, 'a, convallis et ipsum. ')
or die(mysql_error()”);
$result = mysql_query(“SELECT post_id, author, title, content FROM posts WHERE post_id < 6”) or die(mysql_error());

echo "

";
while($row = mysql_fetch_array($result)) {
echo "




";
}
echo "

Post ID	Author	Title	Content
$row[post_id]	$row[author]	$row[title]	$row[content]

";

$result2 = mysql_query(“SELECT post_id, name, email, website, content FROM comments WHERE post_id = 1”) or die(mysql_error());

echo "

";
while($row = mysql_fetch_array($result)) {
echo "



td>$row[website]

";

}
echo "

Post ID	Author	Email	Website	Content
$row[post_id]	$row[name]	$row[email]	$row[content]

";

?>
[/php]

[richei]

Because the table does exist, it was created when you first ran the script. You need to first do a query to see if that table exists. Change NAME_OF_DATABASE to match yours.

[php]
function table_exists($tableName) {

$dbName = 'NAME_OF_DATABASE';

$sql ='SHOW TABLES WHERE Tables_in_' . $dbName . ' = \'' . $tableName . '\'';
$rs = mysql_query($sql);

if(!mysql_fetch_array($rs)) {
	return FALSE;
} else {
	return TRUE;
}

}

if(table_exists($posts) === FALSE) {
mysql_query(“CREATE table posts(post_id INT NOT NULL AUTO_INCREMENT Primary Key, author VARCHAR(32) NOT NULL, title VARCHAR(32) NOT NULL, content VARCHAR(32) NOT NULL, date_published TIMESTAMP(8))”) or die(mysql_error());
}

if(table_exists(comments) === FALSE) {
mysql_query(“CREATE table comments(comment_id INT NOT NULL AUTO_INCREMENT Primary Key, post_id INT NOT NULL, name VARCHAR(32) NOT NULL, email VARCHAR(32) NOT NULL, website VARCHAR(32) NOT NULL,content TEXT NOT NULL,date_published TIMESTAMP(8))”) or die(mysql_error());
}
[/php]

[freeb]

comment_id and post_id are auto increment, but you’re inserting values into this field in both tables…I don’t know if this is OK or not…

If the tables are created each time the script runs, can’t you use mysql_query and put a DROP TABLE posts; and DROP TABLE comments; before each of the create tables code executes? That’s how I used to do it with Oracle in the 90s when creating tables, but I’ve forgotten most everything I learned from back then.

I’m new to php and mysql, so take my comments with a grain of salt.

[php]<?php

include(“dbconnect.php”); // using dbconnect object
$con = new dbconnect();
$con->connect();
mysql_query(“CREATE table posts
( post_id INT NOT NULL AUTO_INCREMENT Primary Key,
author VARCHAR(32) NOT NULL,
title VARCHAR(32) NOT NULL,
content VARCHAR(32) NOT NULL,
date_published TIMESTAMP(8))”)
or die(mysql_error());

mysql_query(“CREATE table comments
(comment_id INT NOT NULL AUTO_INCREMENT Primary Key,
post_id INT NOT NULL,
name VARCHAR(32) NOT NULL,
email VARCHAR(32) NOT NULL,
website VARCHAR(32) NOT NULL,
content TEXT NOT NULL,
date_published TIMESTAMP(8))”)
or die(mysql_error());

mysql_query(“INSERT INTO posts
(post_id, author, title, content)
VALUES(1, ‘John’, ‘First post’, ‘Lorem ipsum dolor sit amet’),
(2, ‘Tom’, ‘Second post’, ‘Sed ullamcorper consectetur ligula’),
(3, ‘Jane’, ‘Third post’, ‘Art’),
(4, ‘Paul’, ‘Fourth post’, ‘Sed ullamcorper consectetur ligula’),
(5, ‘clark’, ‘Fifth post’, ‘Aliquam velit nunc, accumsan id semper a’),
(6, ‘Mary’, ‘Sixth post’, 'habitant morbi tristique senectus ')
or die(mysql_error()”);

mysql_query(“INSERT INTO comments
(comment_id, post_id, name, email, website, content)
VALUES(1, 3, ‘Tom’, ‘[email protected]’, ‘www.google.com’, ‘Pellentesque habitant morbi tristique senectus’),
(2, 1, ‘Peter’, ‘[email protected]’, ‘www.yahoo.com’, ‘convallis et ipsum. Integer’),
(3, 5, ‘Amanda’, ‘[email protected]’, ‘www.metacritic.com’, ‘accumsan id semper a,’),
(4, 1, ‘Clark’, ‘[email protected]’, ‘www.asu.edu’, ‘ipsum neque et nulla’),
(5, 4, ‘Paul’, ‘[email protected]’, ‘www.firefox.com’, ‘Duis elit nisl, laoreet at fringilla’),
(6, 2, ‘Steve’, ‘[email protected]’, ‘www.kudzu.com’, 'a, convallis et ipsum. ')
or die(mysql_error()”);
$result = mysql_query(“SELECT post_id, author, content FROM posts WHERE id<6”);

print("");
print("

");
print("");
print("“);
print(”“);
print(”“);
print(”“);
print(”");
while($row = mysql_fetch_array($result))
{

print("

");
echo " ”;
print("");
}
print("

Id	Author	Title	Content
" . $row[‘id’] . “	” . $row[‘author’] . “	” . $row[‘title’] . “	” . $row[‘content’] . “

");

print("");

$result2 = mysql_query(“SELECT content FROM comments WHERE post_id = 1”);

while($row = mysql_fetch_array($result2))
{
echo $row[‘comment’];
echo “
”;
}
mysql_close($con);
?>[/php]

[richei]

You don’t want to drop the tables if they exist because he wants to keep the data. All he needs to do is run a query to see if those tables exist, and if they don’t, create them. If they do exist, then move on to to the next part.

[Elodin]

Yeah, the problem was that I was supposed to create the tables in myPhPAdmin. For some reason I thought the table would just be recreated if it already existed, but clearly that didn’t work. Thanks for the help.

[richei]

Its not going to recreate it by default. now if you don’t want the data, then you can do that same check, except instead of ignoring the queries, run a drop table query, then redo the table and insert the data.