PHP 101 (part 8): Databases and Other Animals – Part 1

Please remove "or the mysqli object’s query() method, " from the last statement as it doesn’t make sense. The "mysqli" object is already attached to a connection.

I am an elementary stage beginner. Someone installed Mysql and PHP for me. Sure, I find the tutorial very helpful. But now I have a problem. I tried to run the script under ‘Hello Database’ but only received on my browser window this message :
Fatal error: Call to undefined function mysql_connect() in c:Inetpubwwwrootdatabase.php on line 19.
What am I doing wrongly?

I am an elementary stage beginner. Someone installed Mysql and PHP for me. Sure, I find the tutorial very helpful. But now I have a problem. I tried to run the script under ‘Hello Database’ but only received on my browser window this message :
Fatal error: Call to undefined function mysql_connect() in c:Inetpubwwwrootdatabase.php on line 19.
What am I doing wrongly?

I just want to stop to say "Thanks for the great tut!" before continuing with part 8.

On my machine i had to use a different syntax for the command line. i removed all the ”””s from the outside the ()’s, in other words

CREATE TABLE `symbols` (
`id` int(11) NOT NULL auto_increment,

became

CREATE TABLE symbols (
id int(11) NOT NULL auto_increment,

and

INSERT INTO `symbols` VALUES (1, ‘America’, ‘eagle’);

became

INSERT INTO symbols VALUES (1, ‘America’, ‘eagle’);

with this change it worked fine. Hope this helps someone. Love the tutorial.

I set everything up as stated in the tutorial and attempted to run the code from Hello Database. I received the following output:

"; while($row = mysql_fetch_row($result)) { echo ""; echo "".$row[0].""; echo "" . $row[1].""; echo "".$row[2].""; echo ""; } echo ""; } else { // no // print status message echo "No rows found!"; } // free result set memory mysql_free_result($result); // close connection mysql_close($connection); ?>

I can’t for the life of me figure out why the first echo command is causing the rest of the php code to echo rather than perform the while command and move on.

any ideas?

This tutorial is really confusing. You make a table without first connecting to it? I’ve tried all the code here and all i get are errors.

Surely you connect to the database *then* make the table *then* add things.

Don’t be confused

If you go back and read the first part more carefully, you’ll see that the table at the beginning is there to get you used to working with the mysql client *without PHP*. It’s just more useful to know how the thing works ‘in the raw’ before you start connecting to it from a script.

The reason "" doesn’t work is that those aren’t supposed to be quotation marks, but backticks…

That error message means that ext/mysql isn’t enabled in your setup.

Hello,

I got the same outcome as Stowt and I could not find any answer to it. What’s wrong? Could you help me please?

"; while($row = mysql_fetch_row($result)) { echo ""; echo "".$row[0].""; echo "" . $row[1].""; echo "".$row[2].""; echo ""; } echo ""; } else { // no // print status message echo "No rows found!"; } // free result set memory mysql_free_result($result); // close connection mysql_close($connection); ?>

rod

Ps: so far this tutorial has been very helpful to me. Thank you.

Hi, I’m having the same problem as Stowt and Rodgeck:

"; while($row = mysql_fetch_row($result)) { echo ""; echo "".$row[0].""; echo "" . $row[1].""; echo "".$row[2].""; echo ""; } echo ""; } else { // no // print status message echo "No rows found!"; } // free result set memory mysql_free_result($result); // close connection mysql_close($connection); ?>

Does it have anything to do with the PHP version? I’m using 5.0.41
How can I resolve this?? Please help…

Other than that this tutorial is great.

Hi,

This is maybe the 10th tutorial I’ve tried in an attempt to accomplish the same task: adding info to my database/selecting and viewing that data on my site.

I can output basic PHP functions, so I know that works. I also have a Phorum (PHP/MySQL based) message board on my site that interacts with my mySQL DB, so I know that’s working as well.

But these tutorials always give me the same output – a blank page. Any idea what’s happening?

Thanks,
Sarah

Sarah

I had this problem while I was experimenting with connection scripts unrelated to this tutorial.

I found I got a blank page if the database username of password was wrong.

hope that helps

I couldn’t get the very first exercise above, the creation of the first database in MySQL above, to work.

Here is the error message (note that I wrap everything here relating to input code and output, including error messages, with curly brackets):

{Parse error: syntax error, unexpected T_STRING in C:\wamp\www\Tutorial\Part8 (MySQL & Other Databases)\create_database.php on line 6}

And here is the line 6 referred to above: {CREATE DATABASE testdb;}

I also followed the suggestion of rbstimers and eliminated the "back ticks" (the gizmos that look like left-side single quotes, or single "back" quotes if we are comparing these symbols to back- and forward slashes), although rbstimers’ verbalized instructions do not unambiguously correspond to what he actually does, which causes some confusion because one is not quite sure just how thorough one should be with the hatchet (rbstimers says that he removed all of the back ticks which lie "outside the ()’s", which is difficult to interpret, since these parentheses are concatenated, while in practice – in the example that rbstimers gives – rbstimers also removes the back ticks that are inside the outside parentheses, but outside the inside parentheses, i.e., where concatenation occurs); following rbstimers’ example, I ended up removing all of the back ticks except for the ones enclosing the example of {id} immediately following the code {PRIMARY KEY} (with the "create database" code line above as line 6, and with no empty/blank lines in the code (see the entire page of unedited code below), the "primary key" code line would thus correspond to line 11).

Anyways, it didn’t work, also not with either of the two logical variants (not removing the back ticks located inside the outside parentheses but outside the inside parentheses, while removing those located outside the non-concatenated parentheses, and then reversing this procedure), and of course I wrapped the given code with the usual PHP start/end tags and then the usual HTML start/end tags. It just didn’t work. I confess that I may not have been on the same page as rbstimers, since the language rbstimers uses (he refers to using "the command line") suggests that he was using a DOS setup.

In fact this inspired me to access MySQL on my wampserver2 application (accessed from the system tray), in the belief that the creation of the database might need to take place from there, and MySQL in wampserver2 turned out to be a DOS environment all right, but I couldn’t even open it because every time I plugged in the demanded password (I used the password I use to gain access to phpMyAdmin in wampserver2), it gave a loud beep and closed itself. I also created a different user ID as part of the setup of wampserver2 which may have been the ID used to access databases (a second layer of security in wampserver2?), but when I plug that ID’s password (I log onto phpMyAdmin with the second user ID) into MySQL\MySQL console, the same loud beep occurs and the DOS window closes. This may of course be a simple problem of how priveleges (the ability to access and therefore edit) are set up in wampserver2.

I therefore urge anyone who uses wampserver2, which has built-in everything (MySQL, etc.), according to my understanding, and who has managed to get this code to work, to – in the spirit of Master Vikram – "spill the beans" (tell us others what he/she did to make it work).

I will also say that I am just a little bit disappointed with Master Vikram because up to this point we have only dealt with simplistic stuff, and now when we tackle the first really thorny – and very important – issue, the instructions not only seem to be lacking, there is no effort to repair this shortcoming by providing explanatory "support" here in the Comments. I let this criticism cross the lips of my keyboard because something tells me that it should not be necessary to create a database from within a special interface of my wampserver2 – I mean, the whole point of cleverly creating databases with the help of PHP, and with the additional help of the language of SQL, is that it can be done (read: should be possible) in PHP 5 and on a normal windows interface, rather than having to do this in SQL alone, or in another arcane database language, and via DOS.

Of course, there is also still the possibility that in my particular wampserver2 case, this has something to do with the way that wampserver2 has to be set up. That second user ID may need to be accessed before one can use the normal localhost windows interface in order to create databases, i.e., still a problem with "privileges". If anybody has concrete info on this problem pertaining to wampserver2 (no speculation, please, and no confusing contributions from those unfamiliar with wampserver2), then please, in the spirit of Master Vikram, do jump in!

The whole unedited nine yards:

(HTML)
(HEAD) (/HEAD)
(BODY)
(?php)
// PHP 5
CREATE DATABASE testdb;
CREATE TABLE `symbols` (
`id` int(11) NOT NULL auto_increment,
`country` varchar(255) NOT NULL default ”,
`animal` varchar(255) NOT NULL default ”,
PRIMARY KEY  (`id`)
) TYPE=MyISAM;
INSERT INTO `symbols` VALUES (1, ‘America’, ‘eagle’);
INSERT INTO `symbols` VALUES (2, ‘China’, ‘dragon’);
INSERT INTO `symbols` VALUES (3, ‘England’, ‘lion’);
INSERT INTO `symbols` VALUES (4, ‘India’, ‘tiger’);
INSERT INTO `symbols` VALUES (5, ‘Australia’, ‘kangaroo’);
INSERT INTO `symbols` VALUES (6, ‘Norway’, ‘elk’);
?)
(/BODY)
(/HTML)

Yours,
phileasphogg

I may have been a bit hasty in that criticism above – I should have read the exercise through thoroughly before wailing. I have actually progressed some, among other things I realize that the reference to the "command line" is indeed the command line from within some other interface than the localhost one (I got a bit of traction simply using phpMyAdmin, then accessing the database in question, then creating first a "symbols" table by simple hand operations, then a "symbols2" table using Vikram’s code (but changing the table name from "symbols" to "symbols2", since an earlier error report informed me that the "symbols" table already existed). I have yet to be able to get any output, but that will probably be rectified as soon as I read up on Vikram’s/Melonfire’s "Spealing SQL" tutorial. My apologies, Master Vikram!

phileasphogg

I’m sure a lot of yous have seen the above error generated when you tried to create a table in PHP/MySQL from within your text editor. I just can’t figure out what I am doing wrong, and believe me, I h a v e experimented! I think that the syntax problem (the code in a jiffy) has to do with the fact that MySQL adds a column of numbers/integers (i.e., it numbers each row entry), and maybe this gets in the way when one specifies the contents of a table row to be inserted into the table (I create the table first, then in a separate PHP file, I bring up the database/table up and then add the contents).

I have tried not mentioning at all the variables in that table (call it "twinkies" here), i.e., I sometimes just write "INSERT INTO twinkies VALUES(‘blah’, ‘blah’, ‘blah’)", and I have tried to mention all of the variables like so: "INSERT INTO twinkies(*) VALUES(‘blah’, ‘blah’, ‘blah’)", and then like so: "INSERT INTO twinkies(halb, halb, halb) VALUES(‘blah’, ‘blah’, ‘blah’)". Still I get the error message above! I have also tried adding the friggin numbers like Vikram does above, like so: "INSERT INTO twinkies VALUES(1, ‘blah’, ‘blah’, ‘blah’)", and here I get a message about duplicate keys, presumably because the system is trying to remind me that it can count, that I needn’t list the numbers.

Here is the offensive code:

\<HTML\>
\<HEAD\>\</HEAD\>
\<BODY\>
\<?php
// set database server access variables:
$host = "localhost";
$user = "BruiserHeHe";
$pass = "MeHeHeHeHe";
$db = "testdb";
// open connection
$connection = mysql_connect($host, $user, $pass) or die ("Unable to connect!");
// select database
mysql_select_db($db) or die ("Unable to select database!");
// Insert a row of information into the table "twinkies"
mysql_query("INSERT INTO twinkies
(Name, Region, Population) VALUES (‘Afghanistan’, ‘Asia’, ’26000000′) ")
or die(mysql_error());

mysql_query("INSERT INTO twinkies
(Name, Region, Population) VALUES (‘Albania’, ‘Europe’, ’3200000′) ")
or die(mysql_error());

mysql_query("INSERT INTO twinkies
(Name, Region, Population) VALUES (‘Algeria’, ‘Middle East’, ’32900000′) ")
or die(mysql_error());

etc., etc,

mysql_query("INSERT INTO twinkies
(Name, Region, Population) VALUES (‘Zimbabwe’, ‘Africa’, ’12900000′) ")
or die(mysql_error());

?\>

\</BODY\>
\</HTML\>

I hope somebody can respond to this, but please remember to speak slowly and enunciate your words carefully .

phileasphogg

I’m kinda talking to myself here (it is the weekend and geeks are screwing around with other things), but hopefully it will be useful to other noobs like me. I found the solution to my problem on a site that I will refer you to in a jiffy. The problem and solution, in a nutshell, is that beginnger do not specify enough columns and that the all-important "id" column is not specified correctly.

Here is how to do it properly (note the addition of the variable "id" in the twinkies variables list and the corresponding "”" in the VALUES array):

mysql_query("INSERT INTO twinkies
(id, Name, Region, Population) VALUES(”, ‘Afghanistan’, ‘Asia’, ’26000000′) ")
or die(mysql_error());

and here is how I did it IMproperly :

// Insert a row of information into the table "twinkies"
mysql_query("INSERT INTO twinkies
(Name, Region, Population) VALUES (‘Afghanistan’, ‘Asia’, ’26000000′) ")
or die(mysql_error());

Note also that if you make one single mistake – a missing comma or single quote is enough! – the error report will tell you that there is a column match problem at row one, which may cause you to wrack your brain in vain to find the problem on line one/row one (some day MySQL will develop intelligent error reporting, such that problems will be accurately identified/pinpointed, but we are where we are, right?). This is especially relevant if you have a long data list (mine consisted of 193 entries). With such a long data list, locating errors can be daunting. The solution is to INSERT the data in smaller increments (all "A"s, all "B"s, etc.), then when a snag arises it is a heckuva lot easier to spot the mistake.

Also, please note that if you run your table creation file more than once, then when you ask the data to spit out a listing or a table, you can get multiple entries (although I had only 193 entries, my first table had 875 entries!!!!). The solution, if you have been experimenting and may have run the creation file more than once, is to of course go into the MySQL database in question and ask it to drop the table in question, then run the creation file once, the insert file once, then run your output file and you will not have any duplicated entries.

Finally, here is the link I found that fixed the problem – hang in there noobs!:

http://htmlfixit.com/cgi-tutes/tutorial_MySQL_Error_Invalid_Query_Column_Count_Does_Not_Match_Value_Count.php

phileasphogg

I just wrote:

"Here is how to do it properly (note the addition of the variable "id" in the twinkies variables list and the corresponding "”" in the VALUES array):"

Make that {id} and {”} in the above (the font here renders both single and double quotes the same!).

Hang in there noobs!

I would like to thank the author first for his excellent tutorial…

THIS IS A SMALL TIP FOR BEGINNERS ONLY!…

Check your MySQL Version First… i use mysql 5.0 in winXP!

i’ll tackle this step by step!

1. CREATE DATABASE database_name; (ex. CREATE DATABASE testdb;)
Note: don’t forget (;)semi-colon!
2. USE database_name (ex. USE testdb;)
Note: again… DON’T YOU EVER FORGET (;)semi-colon!
3. Now CREATE TABLE table_name (ex. CREATE TABLE symbols)
4. Next, include this part like what this tutorial(zend) says…….

(
id int(11) NOT NULL auto_increment,
country varchar(255) NOT NULL default ”,
animal varchar(255) NOT NULL default ”,
PRIMARY KEY (id)
) TYPE=MyISAM;

hey… do u figure out something?
hehehe…
I REMOVED THE (“)backticks

5. then insert data’s in the table:

INSERT INTO symbols VALUES (1, ‘America’, ‘eagle’);
INSERT INTO symbols VALUES (2, ‘China’, ‘dragon’);
INSERT INTO symbols VALUES (3, ‘England’, ‘lion’);
INSERT INTO symbols VALUES (4, ‘India’, ‘tiger’);
INSERT INTO symbols VALUES (5, ‘Australia’, ‘kangaroo’);
INSERT INTO symbols VALUES (6, ‘Norway’, ‘elk’);

Note: i have removed again the (“) backtikcs in table_name(symbols)

6. and the LAST PART is testint the table if it’s working!
i have used the command:

select * from symbols;

7. that’s all folks!

8. this is the outcome of mine!

+—-+———–+———-+
| id | country | animal |
+—-+———–+———-+
| 1 | America | eagle |
| 2 | China | dragon |
| 3 | England | lion |
| 4 | India | tiger |
| 5 | Australia | kangaroo |
| 6 | Norway | elk |
+—-+———–+———-+
6 rows in set (0.00 sec)

9. hope this works to you too!

——-THIS IS THE ERROR MESSAGE ———

Warning: mysql_connect() [function.mysql-connect]: Access denied for user ‘test’@'localhost’ (using password: YES) in C:\Program Files\Apache Group\Apache2\htdocs\sqltutorial.php on line 17
Unable to connect!

—— i get this error when i try this tutorial ——–

<html>
<head>
<basefont face="Arial">
</head>
<body>

<?php

// set database server access variables:
$host = "localhost";
$user = "test";
$pass = "test";
$db = "testdb";

// open connection
$connection = mysql_connect($host, $user, $pass) or die ("Unable to connect!");

// select database
mysql_select_db($db) or die ("Unable to select database!");

// create query
$query = "SELECT * FROM symbols";

// execute query
$result = mysql_query($query) or die ("Error in query: $query. ".mysql_error());

// see if any rows were returned
if (mysql_num_rows($result) > 0) {
// yes
// print them one after another
echo "<table cellpadding=10 border=1>";
while($row = mysql_fetch_row($result)) {
echo "<tr>";
echo "<td>".$row[0]."</td>";
echo "<td>" . $row[1]."</td>";
echo "<td>".$row[2]."</td>";
echo "</tr>";
}
echo "</table>";
}
else {
// no
// print status message
echo "No rows found!";
}

// free result set memory
mysql_free_result($result);

// close connection
mysql_close($connection);

?>

</body>
</html>

——- WHAT SHALL I DO? ———-
KINDLY ANSWER THIS ONE PLEASE…. AND MAKE IT CLEAR WHY DO I GET THIS ERROR PLEASE!

THANKS A LOT!

just put available things right after equal sign, i mean instead default things put your data

NOW I FIGURED IT OUT!

it was pretty much fine! and working!

thanks…. excellent tutorial!

Just in case you’re on a Mac and having the same problem I was.
Mysql through the terminal using command/path.

/Applications/MAMP/Library/bin/mysql -v

Oops, spoke too soon. This is the one that allowed me to create a database…
sudo /Applications/MAMP/Library/bin/mysql -uroot -proot

i followed the instructions, but got this warning

Warning: mysql_connect() [function.mysql-connect]: Access denied for user ‘test’@'localhost’ (using password: YES) in C:\xampp\htdocs\mysqltest.php on line 16
Unable to connect!

i saw the reply to put in your own information, but i downloaded xampp i never had to sign up or register for the mysql database, so how can i put in a password? user name? not possible if i never had one, and apparerntly the "test" default doesn’t work, am i doing something wrong? i know i’ve got mysql on my computer but if i can’t access it, then it’s not going to do me a lot of good, can you help me? i would appreciate any help, thank you

Quote ("i saw the reply to put in your own information, but i downloaded xampp i never had to sign up or register for the mysql database, so how can i put in a password? user name? not possible if i never had one, and apparerntly the "test" default doesn’t work, am i doing something wrong? i know i’ve got mysql on my computer but if i can’t access it, then it’s not going to do me a lot of good, can you help me? i would appreciate any help, thank you")

Answer:
You need to make sure if you have an access to your MySQL database.

If you have an access to the database, then you can proceed like this:

1. On your command line prompt, type : "mysql -u root -p"
2. Then enter your MySQL password in there.
3. You will go directly to "mysql>" command line prompt
4. Type "CREATE DATABASE testdb;" (remember to put semicolon ";" at the end of ALL your statements)
5. Type "USE testdb;"
6. Then, you start to type ALL of this into your "mysql>" command line:
<code>
CREATE TABLE `symbols` (
`id` int(11) NOT NULL auto_increment,
`country` varchar(255) NOT NULL default ”,
`animal` varchar(255) NOT NULL default ”,
PRIMARY KEY (`id`)
) TYPE=MyISAM;

INSERT INTO `symbols` VALUES (1, ‘America’, ‘eagle’);
INSERT INTO `symbols` VALUES (2, ‘China’, ‘dragon’);
INSERT INTO `symbols` VALUES (3, ‘England’, ‘lion’);
INSERT INTO `symbols` VALUES (4, ‘India’, ‘tiger’);
INSERT INTO `symbols` VALUES (5, ‘Australia’, ‘kangaroo’);
INSERT INTO `symbols` VALUES (6, ‘Norway’, ‘elk’);
</code>
7. After that, you need to justify your "database server access variables":

$host = "xxxxxxxx"; // you need to put localhost if you host MySQL on your harddrive, but if you host it on another network, you need to specify the IP address
$user = "xxxxxxxx"; // your MySQL username
$pass = "xxxxxxxxxxx"; // your MySQL password
$db = "testdb"; // this is the database name
8. Then, try to run this. Usually if "Access Denied" happens, you do not have any access or any privileges to the database.

HTH

thank you for the help, it turned out i had a bad version of the MySQL a friend helped me get a better version and now i’ve no trouble making tables or accessing the database.

If you are getting the access denied for user ‘test’@'localhost’ error, or if you forgot your sql username/password for some reason, you can simply use this sql command to add the test user to the testdb:

GRANT ALL ON testdb.* TO ‘test’@'localhost’ IDENTIFIED BY ‘test’;

There is a clear problem with the while loop that you are using. It is randomly omitting one row of data, and It does it both on my while loop and even in your tutorial while loop omits row #2 China. What is going on with that weird line. Every other line shows perfectly but not that one.

Chuck
cjrussell@twentyseconddev.com

I ran acrost this problem when I saved my file as a .html . Simply rename your file to a .php to fix this!

P.s. Best coding tut seris I have ever read! Thanks Vikram!

I’m running locally through MAMP and was able to create the intial table in phpMyAdmin by 1st creating the testdb then copying the SQL data in manually line for line. I also had to create the test user and grant all privileges.

Great tutorials Vikram!!!

- Matt

It all worked fine until I reached the following line:
INSERT INTO symbols VALUES (1, ‘America’, ‘eagle’);

and I got an error message:
ERROR 1146 (42S02): Table ‘world.symbols’ doesn’t exist

Could anyone please help me with this?

Thanks,

This is actually kind of the first programming language I learned, and I really enjoyed the tutorials.

I tried the hello database code with the following lines changed:

$host = "localhost";
$user = "root";
$pass = "12345";
$db = "whatever";

but I got the message, Unable to select database!

I have no idea how to fix this problem.

Thanks for all your help in advance!

mysqli – how does $result->mysqli->query($query) allow $result to become and object and act like an objec as in
$result->num_rows > 0
that means $result is an object using
a method num_rows
please see further details:

Please look at the section below, pertains to the last section in this article, I have also included beginning text and some lines from the code that I have a question

"You can also use ext/mysqli in an object-oriented way, where each task – connecting, querying, fetching – is actually a method of the mysqli() object:

// execute query
if ($result = $mysqli->query($query)) {
// see if any rows were returned
if ($result->num_rows > 0) {
// yes
// print them one after another
echo "<table cellpadding=10 border=1>";
while($row = $result->fetch_array()) {
echo "<tr>";
echo "<td>".$row[0]."</td>";
echo "<td>".$row[1]."</td>";
echo "<td>".$row[2]."</td>";
echo "</tr>";
}
echo "</table>"; "

at the beginning of the code segment above
($result = $mysqli->query($query)) does $result become an object as well
a few down below that there is another line that is
while($row = $result->fetch_array())
in the line above the syntac $result->fetch_array() means
$result is an object , is it?
if so how did it become an object since it is trying to
use the fetch_array() method of the class/object mysqli
I AM VERY CONFUSED AND STUCK AT THIS POINT
BECAUSE I CAN’T UNDERSTAND how this is working.
Can someone please help. I desparately need help with this one.
Perhaps I missed something when i was reading the 2 articles
about classes, I have read them again and again.
But still can determine how the variable $return is useing the
methods/functions within the class /object $mysqli.

hello,
thanks for that useful code but i have one query about that code as follows
– How to add 1 column with submit button to every row of table of that retrived data???

Everyone getting…

"; while($row = mysql_fetch_row($result)) { echo ""; echo "".$row[0].""; echo "" . $row[1].""; echo "".$row[2].""; echo ""; } echo ""; } else { // no // print status message echo "No rows found!"; } // free result set memory mysql_free_result($result); // close connection mysql_close($connection); ?>

or similar, you need a .php extention, not .html