• (2 Pages)
  • +
  • 1
  • 2

mySQL & PHP: A Beginners Look Rate Topic: ***** 1 Votes

#1 skyhawk133  Icon User is offline

  • Head DIC Head
  • member icon

Reputation: 1875
  • View blog
  • Posts: 20,282
  • Joined: 17-March 01

Posted 03 December 2004 - 04:51 PM

*
POPULAR

mySQL & PHP: A Beginners Look

As in many languages, there are 150 different ways to do just about everything, the same rule applies for a simple connection and output of data in a mySQL database using PHP. This tutorial will give you only one way of accomplishing this, and while it works, it is definitely not the only way.

We'll assume you already have some sort of database setup, whether it's from a pre-installed script, or a database of your own creation, it doesn't matter. It should at least have a few records in it for testing purposes.

For the sake of this tutorial, we will place all of the code in one file, however, it is good practice to place the connection variables and connection string in a separate config type file which can be included each time you make the database connection.

So lets get started, first we should define out server, username, password, and the database we will be connecting to. The easiest way to do this is with a few variables:

<?php
// MySQL Connection Information
$server = "localhost";                // The name of your server
$username = "username";          // The MySQL username
$password = "password";         // The MySQL password
$dbname = "database_name";               // The name of the database
?>


Once we have our connection variables, we can go ahead and make the physical connection to the database server and select the database to use:

<?php        
// mySQL Connect String
$connection = mysql_connect("$server","$username","$password");

// Select the database
mysql_select_db("$dbname");
?>


Alrighty, so now we've got an open connection to the database, from this point we can pass SQL commands. For this tutorial we'll just be using a SELECT statement. However, this is where you would put your INSERT, UPDATE, or DELETE statement depending on what you were trying to accomplish.

<?php
$sql = "SELECT id, username, email, posts FROM members;";

// execute SQL query and get result
$sql_result = mysql_query($sql,$connection)
     or die("Query $sql failed: " . mysql_error()); 
?>


This will result in all records from the 'members' table being retrieved and stored in the $sql_result variable. Since we've selected the id, username, and email columns, we will need to pull them back out in to a usable form. This is done using the mysql_fetch_array function:

<?php
// Loop through the data set and extract each row in to its own variable set

while ($row = mysql_fetch_array($sql_result)) {
     extract($row);
?>


This is the beginning of the loop which will pull each row from the $sql_result variable. Each time the while loop runs, it goes to the next row. You will notice we are using extract($row) in the above code, what does this do you ask? In most tutorials you read, data in each column is referenced like this: $row['username'], well, that's one too many keys to press, so by using extract(), we are able to pull each column in to its own variable. So, instead of $row['username'], we use $username to reference data in the username column. Now, let's take the piece of code above, and make it actually show us something:

<?php
// Loop through the data set and extract each row in to its own variable set

while ($row = mysql_fetch_array($sql_result)) {
     extract($row);
?>
    <li>Username: <?php echo $username; ?> (<?php echo $email; ?>)
<?
// End while loop
}
?>

So what exactly did we just do? We looped through the entire dataset and each time we made a bullet with the username and the email address in parentheses:

Quote

Username (email@domain.com)
Username2 (email2@domain.com)
Username3 (email3@domain.com)


You'll notice that instead of using one big echo, like this:

echo ("Username: $username ($email)");


We instead opted to close our PHP tag and write basic HTML with nested <?php echo $variable; ?> statements inside. This makes reading the code a little easier, and also makes the code process faster because only code in between the <?php and ?> is sent to PHP.

So that's it, you've connected to the database, executed a SQL statement, retrieved the records, and displayed them using the actual column name as the variable.

Is This A Good Question/Topic? 8
  • +

Replies To: mySQL & PHP: A Beginners Look

#2 Artanis  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 28-July 06

Posted 28 July 2006 - 05:50 PM

oki, this looks great!, going to read this tutorial now :)
Was This Post Helpful? 0
  • +
  • -

#3 chrismoney  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 16
  • Joined: 25-August 06

Post icon  Posted 31 August 2006 - 12:45 AM

Thanks for the tutorial, I am reading it and now I am starting to playing with mySQL and PHP because I am doing that also in my class. YOU ARE THE BEST.


CHRISMONEY
Was This Post Helpful? 0
  • +
  • -

#4 Demon  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 05-February 07

Posted 05 February 2007 - 10:33 PM

this is a very nice tutorial i am goin to try to make some if i have time with college and work :P
Was This Post Helpful? 0
  • +
  • -

#5 maggie_noodles  Icon User is offline

  • pen and paper slave

Reputation: 1
  • View blog
  • Posts: 741
  • Joined: 08-November 06

Posted 05 March 2007 - 07:01 AM

VERY VERY helpful! :^:
Was This Post Helpful? 0
  • +
  • -

#6 thepcpro  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 11-May 07

Posted 11 May 2007 - 03:10 PM

View Postmaggie_noodles, on 5 Mar, 2007 - 07:01 AM, said:

Didn't work for me
:^:

Was This Post Helpful? 0
  • +
  • -

#7 fuzzyjello  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 19
  • Joined: 13-April 07

Posted 24 July 2007 - 11:23 AM

Thanks! This was very helpful.
Was This Post Helpful? 0
  • +
  • -

#8 sharlene_c  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 11
  • Joined: 01-February 09

Posted 01 February 2009 - 08:18 PM

Thank you sooo much for this tutorial! I've been searching and trying things for weeks and I finally have some results to play with that I understand. I'm a total newbie to mySQL and PHP so no doubt i'll be back here for lots of help but just wanted you to know i feel like i've found a 'home' lol. Thanks again :)
Was This Post Helpful? 0
  • +
  • -

#9 sfw  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 27
  • Joined: 24-April 08

Posted 25 March 2010 - 03:04 PM

excellent, easy to follow and very usable. thanks
Was This Post Helpful? 1
  • +
  • -

#10 amda2008  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 01-April 10

Posted 01 April 2010 - 03:52 AM

Neatly explained. Thank you very much

Was This Post Helpful? 0
  • +
  • -

#11 al-yamamah  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 02-February 10

Posted 06 April 2010 - 11:04 PM

thx a lot ..

This post has been edited by al-yamamah: 06 April 2010 - 11:19 PM

Was This Post Helpful? 0
  • +
  • -

#12 Pinky Rose  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 13-April 10

Posted 03 May 2010 - 06:02 PM

Great Topic..very helpful for beginners & easy to follow..

Thanks A lot,,
Was This Post Helpful? 0
  • +
  • -

#13 sulcuts  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 15-June 10

Posted 12 July 2010 - 10:08 PM

was vewi vewi helpful. thanx a quadrillion. :-D

This post has been edited by sulcuts: 12 July 2010 - 10:10 PM

Was This Post Helpful? 0
  • +
  • -

#14 saiqa_ gee  Icon User is offline

  • New D.I.C Head

Reputation: -3
  • View blog
  • Posts: 28
  • Joined: 25-August 10

Posted 28 August 2010 - 11:45 AM

this was very helpful but i have to ask what i should do
if i want to display that this usernane already exists in data base....
Was This Post Helpful? 0
  • +
  • -

#15 saiqa_ gee  Icon User is offline

  • New D.I.C Head

Reputation: -3
  • View blog
  • Posts: 28
  • Joined: 25-August 10

Posted 28 August 2010 - 12:09 PM

hello i need this plz help me.....
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2