Connecting PHP page with Database MySQL

TiKaY

Hi, I am currently trying to connect a page with a database MySQL. I cannot find a way to succeed... I tried to use this example, but I always get errors. Also, I am not sure what information should I replace in this following code :

<?php 
$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error 
connecting to mysql'); 
mysql_select_db($dbname); 
$int_y_pos = -1; 
$int_y_step_small = 1; 
$sql = "SELECT count(id) AS count, DATE_FORMAT(date, '%Y, %m-1, %d') 
AS date FROM dtt WHERE date NOT LIKE '0000-00-00 00:00:00' AND date 
NOT LIKE '2000-00-00 00:00:00' GROUP BY DATE_FORMAT(date, '%Y, %m, 
%d')"; 
$sql = mysql_query($sql); 
$rownum = mysql_num_rows($sql); 
?> 
<html> 
  <head> 
    <script type="text/javascript" src="http://www.google.com/jsapi"></ 
script> 
    <script type="text/javascript"> 
      google.load("visualization", "1", {packages: 
["annotatedtimeline"]}); 
      google.setOnLoadCallback(drawData); 
      function drawData() { 
        var data = new google.visualization.DataTable(); 
        data.addColumn('date', 'Date'); 
        data.addColumn('number', 'Repairs'); 
<?php 
echo "     data.addRows($rownum);\n"; 
while($row = mysql_fetch_assoc($sql)) { 
        $int_y_pos += $int_y_step_small; 
        echo "     data.setValue(" . $int_y_pos . ", 0, new Date(" . 
$row['date']  . "));\n"; 
        echo "     data.setValue(" . $int_y_pos . ", 1," . $row['count'] . "); 
\n"; 
} 

?> 
        var time = new 
google.visualization.AnnotatedTimeLine(document.getElementById('time_div')) ; 
        time.draw(data, {displayExactValues:true}); 
      } 
    </script> 
  </head> 
  <body> 
    <div id="time_div" style="width: 940px; height: 240px;"></div> 
  </body> 
</html>

Can anyone help me?

dagon

you need to define the variables $dbhost, $dbuser, $dbpass

 $dbhost= 'something';

etc

TiKaY

I already did that, but still didnt work, it says Error with this line :
$rownum = mysql_num_rows($sql);

dagon

what exactly is the error?

TiKaY

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/website/public_html/table/TZ/example.php on line 12

laserlight

For debugging purposes, change:

$sql = mysql_query($sql);

to:

$sql = mysql_query($sql) or die(mysql_error());

By the way, is there any reason why you are using the MySQL extension instead of say, the MySQLi extension or the PDO extension?

TiKaY

laserlight;10957706 wrote:
For debugging purposes, change:
$sql = mysql_query($sql);
to:
$sql = mysql_query($sql) or die(mysql_error());
By the way, is there any reason why you are using the MySQL extension instead of say, the MySQLi extension or the PDO extension?

To be honest, I am a newbie in the coding world, especially php. My task was just to connect MySQL database with php coding. I am still learning 😛 . For the ' MySQLi extension or the PDO extension', I dont really know them.

Thanks for your reply, here's the update :
It is now blank (when i preview in browser) , and another question comes to me :
The previous code that i asked for help is a 'template' to show a google chart visualization. If I only want to see the table from the MySQL database, what should i erase/keep from that code?

sneakyimp

The on-line php documentation is the best I've ever encountered. You can learn plenty about functions like [man]mysqli_query[man] there. the mysqli functions are just like the mysql ones but improved. Thus the "i". They also have OOP versions but I'm guessing that will just confuse you.

If you stick with the plain mysql_ functions you'll be ok.

The reason laserlight told you to add the "or die" bit is so that you could check for errors. Just calling mysql_query and not checking to see if it works is a very good way to have broken code and not understand why.

If you are getting blank output from your screen, it sounds like you have a bug in your code. I would highly recommend starting with a little code and gradually expanding it -- this way, if you run into errors you know it's the most recently added bits.

Try making sure your first bit of code works first. I would just try running this script -- but make sure that the $db vars have something in them:

<?php
$conn = mysql_connect($dbhost, $dbuser, $dbpass)
  or die ('Error connecting to mysql');
mysql_select_db($dbname)
  or die('Could not select ' . $dbname);
$int_y_pos = -1;
$int_y_step_small = 1;
$sql = "SELECT count(id) AS count, DATE_FORMAT(date, '%Y, %m-1, %d') AS date
  FROM dtt
  WHERE date NOT LIKE '0000-00-00 00:00:00'
    AND date NOT LIKE '2000-00-00 00:00:00'
  GROUP BY DATE_FORMAT(date, '%Y, %m, %d')";
$result = mysql_query($sql)
  or die('Query failed:' . mysql_error() . "\n$sql");
$rownum = mysql_num_rows($result);

echo "complete, rownum=$rownum";
?>

EDIT: I had a bug in the originally posted code. I'm assigning the query result to $result instead of $sql.

TiKaY

Hi, thanks for the reply, I have no more problems connecting to the MySQL Database!

However, I can`t really find a way to show the exact table that I've made in MySQL.

Here is what I tried :

<?php
$conn = mysql_connect($dbhost, $dbuser, $dbpass)
or die ('Error connecting to mysql');
mysql_select_db($dbname)
or die('Could not select ' . $dbname)
$int_y_pos = -1;
$int_y_step_small = 1;
$sql = "SELECT * [Name of the table]";
$result = mysql_query($sql)
$table = '[name of the table]';
function mysql_table_exists($table, $link)
{
$exists = mysql_query("SELECT 1 FROM $table LIMIT 0", $link);
if ($exists) return true;
return false;
}

I did some research and found that it was the quickest/simplest way to show the table. But it gives me this :

" Parse error: syntax error, unexpected T_VARIABLE in [Path]/example-10-2.php on line 10 "

sneakyimp

line 10 lacks a semicolon at the end.

TiKaY

Line 10 is : $table = '[name of the table]'; it already has a semicolon
If i misunderstood, and line 10 was this :
function mysql_table_exists($table, $link) I already tried to put semicolon and it does nothing.
Now, when i preview again, it says
Parse error: syntax error, unexpected T_VARIABLE in [Path]/example-10-2.ph on line 6

sneakyimp

Learning to find syntax errors is the most basic skill required to write PHP. Look at the line right before line 10. Do you see a semicolon at the end?

$result = mysql_query($sql)

TiKaY

I've already tried that, still the same error.

sneakyimp

Tried what? Adding a semicolon to that line?

dagon

post your full current code, inside the boards php tags ans any error message you get.

TiKaY

<?php 
$conn = mysql_connect($dbhost, $dbname, $dbpass) or die ('Error connecting to mysql'); 
mysql_select_db($dbname) or die('Could not select ' . $dbname);

$int_y_pos = -1; 
$int_y_step_small = 1; 
$sql = "SELECT * FROM [name of the table]";
$result = mysql_query($sql);
$table = '[name of the table]';

function mysql_table_exists($table, $link)
{
    $exists = mysql_query("SELECT 1 FROM `$table` LIMIT 0", $link);
    if ($exists) return true;
        return false;
}
?>

I used this code to show the table that I created in MySQL

dagon

LIMIT 0 will always return you 0 rows. ans you said there was an error?

SQL SHOW TABLES [FROM db_name] [LIKE 'pattern']

TiKaY

dagon;10957828 wrote:
LIMIT 0 will always return you 0 rows. ans you said there was an error?

SQL SHOW TABLES [FROM db_name] [LIKE 'pattern']

Thanks for your reply,
So the code should be like this?

<?php 
$conn = mysql_connect($dbhost, $dbname, $dbpass) or die ('Error connecting to mysql'); 
mysql_select_db($dbname) or die('Could not select ' . $dbname); 

$int_y_pos = -1; 
$int_y_step_small = 1; 
$sql = "SELECT * FROM [name of the table]"; 
$result = mysql_query($sql); 
$table = '[name of the table]'; 

function mysql_table_exists($table, $link) 
{ 
    $exists = mysql_query("SELECT 1 FROM `$table`", $link); 
    if ($exists) return true; 
        return false; 
} 
SQL SHOW TABLES [FROM $dbname] [LIKE 'pattern']
?>

1 - What is a pattern? Could you give me an example?
2 - How do we include the SQL SHOW TABLES?

sneakyimp

Your script is not valid PHP. This line is going to cause an error:

SQL SHOW TABLES [FROM $dbname] [LIKE 'pattern']

You might be better off starting with a basic php tutorial.

dagon

and a basic mysql tutorial