PHP errors loading MySQL

Question

So, I've been having a bit of trouble getting MySQL to work.

My Apache server is up and running, PHP is properly installed, and my MySQL server status is running.

When I call phpinfo() it tells me where the mysql.sock is located, both the folder and file exist.

I'm running OSX 10.8.4

I'm following this tutorial to try to get a grasp of how chats would work: http://www.ibm.com/developerworks/library/x-ioschat/

So when I run my php script, the page loads with errors. I'm new to PHP, and am having a difficult time debugging, installation was particularly troublesome.

So if I keep content-type: text/xml uncommented I get this error:

error on line 2 at column 1: Document is empty

It also tells me that the page is rendered up to the first error, so it makes sense that the page is blank when I load it.

When it is commented, the errors begin to make a bit more sense; however, given my absolute newbish nature to PHP I'm not really sure how to navigate them.

Here are the errors:

Notice: Undefined index: past in /messages.php on line 6

-- This makes sense, I think. I haven't built the client side yet, so there should be no past variable provided.

Warning: mysql_fetch_assoc() expects parameter 1 to be resource, boolean given in /messages.php on line 16

--So, my background is client side, so what this tells me is that my $result variable has a boolean stored in it, instead of whatever is supposed to be in it for the mysql_fetch_assoc() function call. Since the else statement should trigger, that means that either mysql_query() is not working how properly, or my parameter for it is incorrect. I don't know which (if either), nor do I know the solution to either.

Warning: mysql_free_result() expects parameter 1 to be resource, boolean given in /messages.php on line 24

--Again, same as above; semi-makes sense, very unsure how to go about fixing it.

So, after the errors, nothing is displayed below. Which makes sense because the conditions are written under the assumption they'll have a resource in it, not a boolean (I think??)

In my php file, if you compare it with the tutorial you'll see I took out the htmlentities() calls because I read on StackOverflow that they are not needed, and they didn't change the state of the errors I was getting either way.

Anyway, thanks so much for any advice/help given! Here is my code so far:

chat.sql:

DROP TABLE IF EXISTS chatitems;
CREATE TABLE chatitems (
id BIGINT NOT NULL PRIMARY KEY auto_increment,
added TIMESTAMP NOT NULL,
user VARCHAR(64) NOT NULL,
message VARCHAR(255) NOT NULL
);

messages.php:

<?php
ini_set('display_errors','1');
//header( 'Content-type: text/xml' );
mysql_connect( 'localhost:/private/var/mysql/mysql.sock', 'root', '' );
mysql_select_db( 'http://localhost/Documents/JoistChat/chat.sql' );
if ( $_REQUEST['past'] ) {
    $result = mysql_query('SELECT * FROM chatitems WHERE id > '.
    mysql_real_escape_string( $_REQUEST['past'] ).
    ' ORDER BY added LIMIT 50');
} else {
    $result = mysql_query('SELECT * FROM chatitems ORDER BY added LIMIT 50' );    
}
?>
<chat>
<?php
while ($row = mysql_fetch_assoc($result)) {
?>
<message added="<?php echo( $row['added'] ) ?>" id="<?php echo( $row['id'] ) ?>">
    <user><?php echo( $row['user'] )  ?></user>
    <text><?php echo( $row['message'])?></text>
</message>
<?php
}
mysql_free_result($result);
?>
</chat>

test.html:

<html>
<head>
<title>Chat Message Test Form</title>
</head>
<body>
<form action="http://localhost/JoistChat/messages.php"
method="POST">
User: <input name="user" /><br />
Message: <input name="message" /><br />
<input type="submit" />
</form>
</body>
</html>

1. You are using [an **obsolete** database API](http://stackoverflow.com/q/12859942/19068) and should use [a modern replacement](http://php.net/manual/en/mysqlinfo.api.choosing.php). 2. Where did you see that `htmlentities` is not needed? It is needed (actually, `htmlspecialchars` is better), but for reasons unrelated to the problem you are having. See [this question](http://stackoverflow.com/questions/1996122/how-to-prevent-xss-with-html-php/1996141#1996141). — DCoder, Aug 13 '13 at 06:50
3. Debugging 101: if you get a value you didn't expect (a boolean), go back to where you acquired this value (to `mysql_query()`), consult [the manual](http://us.php.net/mysql_query#refsect1-function.mysql-query-returnvalues) to see what would cause the function to return that, go from there. User comments in that manual page will also be helpful. This specific error is definitely in the top 5 most frequently asked questions on SO... — DCoder, Aug 13 '13 at 06:51
+1 Well asked question. Relevant detail, code and error messages as well as attempts to understand/explain the errors produced. — diestl, Aug 13 '13 at 06:55
possible duplicate of [mysql\_fetch\_array() expects parameter 1 to be resource, boolean given in select](http://stackoverflow.com/questions/2973202/mysql-fetch-array-expects-parameter-1-to-be-resource-boolean-given-in-select) — John Conde, Aug 14 '13 at 00:23

Your Common Sense · Answer 1 · 2013-08-13T07:07:15.657

$past = '';
if ( !empty($_REQUEST['past']) ) {
    $past = 'WHERE id > '.intval($_REQUEST['past']);
}
$sql = 'SELECT * FROM chatitems $past ORDER BY added LIMIT 50'
$result = mysql_query($sql) or trigger_error(mysql_error()." [$sql]");

You need to learn basic coding culture. Basic guidelines are

Do no repeat yourself. You are writing all the stuff twice
Always check for error
Do not stack too much code in one line. Make your code distinct, step by step. Need a program to create an SQL query? Okay. get the product of this code in a variable and pass it over. Do not fold all the program in a single line.
Format your SQL properly

This way you'll always know the reason of the problem and have your code run smooth and easier to maintain.

Also just noted that your way of selecting a database is quite a... strange. 'chat.sql' is a file with table creation code, not database. You have to create a database first, then create a table in it, then select newly created database.

As you have been told already, mysql ext is obsoleted. You have to use use PDO instead, as it's the only choice for PHP users whose only idea of database interaction is direct calls to API.

First, create a file with connection options, pdo.php

<?php
$dsn = "mysql:host=localhost;dbname=test;charset=utf8";
$opt = array(
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
);
$pdo = new PDO($dsn,'root','', $opt);

Then make your code this way

<?php
ini_set('display_errors','1');
//header( 'Content-type: text/xml' );
include 'pdo.php';

$past = '';
$bind = array();
if ( !empty($_REQUEST['past']) ) {
    $past = 'WHERE id > ?';
    $bind = array($_REQUEST['past']);
}
$sql = 'SELECT * FROM chatitems $past ORDER BY added LIMIT 50'
$stm = $pdo->prepare($sql);    
$stm->execute($bind);
$data = $stm->fetchAll();
?>
<chat>
<?php foreach ($data as $row) { ?>

diestl · Answer 2 · 2013-08-13T07:03:25.167

Since the 'past' index is not set, the boolean condition in the if-statement evaluates to false. As a result the else part is executed.

Your error messages tell you that the query

SELECT * FROM chatitems ORDER BY added LIMIT 50

resulted in a SQL error. Try running that query in your MySql client directly to see what the error is. My guess is that the table/database you are accessing is not created/selected.

PHP errors loading MySQL

2 Answers2