Welcome, Guest: Join Nairaland / LOGIN! / Trending / Recent / New
Stats: 2,352,286 members, 5,216,746 topics. Date: Saturday, 19 October 2019 at 10:39 AM

Connecting To A Mysql Database Using Php - Programming - Nairaland

Nairaland Forum / Science/Technology / Programming / Connecting To A Mysql Database Using Php (1643 Views) (2) (3) (4)

(1) (Reply) (Go Down)

Connecting To A Mysql Database Using Php by pintogen: 11:45am On Jan 13, 2009
Connecting to a mySQL database using PHP

Connecting to a mySQL database through PHP first requires knowing what a few parameters are. I suggest making these parameters variables that you can easily change, so that if these parameters change you don't have to go in and alter hundreds (or worse thousands) of mySQL connections inside your scripts. Keep these variables in a setup file and require it or at the very top of scripts you write so you can change them.

// change below is your assigned mySQL username
$user = " ";

// change to the pw below is your assigned mySQL password
$pw = " ";

// change to the database you have permission to connect to
$db = " ";

If you don't know what these values are then consult your host to help you. They should be able to tell you what these parameters are.

SHOW available tables in mySQL database

Similar to opening a file to write to it, you have to open a connection to mySQL before you can do anything. The syntax of this function is as follows:

mysql_connect("localhost OR hostname:port", "httpd OR username", "" or "password"wink;

<?
$mysql_access = mysql_connect("localhost", "username", "password"wink;
mysql_close($mysql_access);
?>

The function mysql_close is unnecessary unless you are setting a persistent connection. To set a persistent mySQL connection use:

mysql_pconnect("localhost", "username", "password"wink;

You would want to use persistent connections where you would have a lot of simulataneous connections from the same user through the script.

The following script below will open your mySQL database connection and show the tables available to you in your mySQL database. You must always open a connection to the mySQL database before doing anything else (you only need to open it at the beginning of a script, and then you can run multiple queries if you want). This is good for testing your ability to successfully access the mySQL database.

$mysql_access = mysql_connect("localhost", $user, $pw);
mysql_select_db($db, $mysql_access);

$result = mysql_query("SHOW tables", $mysql_access);
while($row = mysql_fetch_row($result))
{
print("$row[0]<br>"wink;
}

Using telnet to CREATE tables in a mySQL database

Before you can begin to add data into your mySQL database you need to create a table. You can do this through a script or through telnet. Here's how to do it using telnet.

1. login into telnet.
2. at prompt enter the mysql monitor by typing mysql -p
3. at the password prompt enter your mySQL password.
4. You will be prompted by the mySQL monitor. Type use DATABASENAME where DATABASENAME is the name of your mySQL assigned database.
5. Cut and paste the test table below exactly as below:

CREATE TABLE email
(
ID INT NOT NULL AUTO_INCREMENT,
email VARCHAR(35) NOT NULL,
PRIMARY KEY (ID)
);

You should receive the message like 0 rows affected. Now try entering: SHOW tables;
and you should see the table email now is present. We will use this table for our demonstration below.

How to INSERT new rows (new data) into a table in the mySQL database

The following PHP script (save it as add_email.php3) below demonstrates how to open your mySQL database connection and insert a new email address into the created table email in a mySQL database using PHP scripting. It provides a built-in form to enter email addresses into.

<?
if($email) {
// if $email valid format add email to database
if(ereg("^.+@.+\\, +$", $email))
{
$mysql_access = mysql_connect("localhost", $user, $pw);
mysql_select_db($db, $mysql_access);
$query = "INSERT INTO email ";
$query .= "VALUES(0, '$email')";
mysql_query($query, $mysql_access);
print("successfully added your email to the mySQL database!"wink;

} else { print("sorry your email address does not appear valid"wink; }

} else {

print("<form method=\"POST\" action=\"add_email.php3\">"wink;
print("Please enter your email address: "wink;
print("<input type=\"text\" name=\"email\" size=\"30\">"wink;
print("<input type=\"submit\" value=\"submit\"></form>"wink;
}
?>

How to QUERY rows (existing data) in a table in the mySQL database

Queries can be executed by using the PHP mysql_query function. First you need to build the query. Let's say we entered in abc@123.com into the script above and my email is in the database. Here is how we'd run a query to find if it was entered into the database successfully:

$query = "SELECT email FROM email WHERE email='abc@123.com' ";
$result = mysql_query($query, $mysql_access);
if(mysql_num_rows($result)) {
// it is true (the email exists)
print("<strong>$email</strong> exists in the database."wink;
} else {
// false, so it doesn't exist
}

Now what if we wanted to find all emails from the tdscripts.com domain? Use the code below:

$query = "SELECT email FROM email LIKE '%tdscripts.com' ";
$result = mysql_query($query, $mysql_access);
if(mysql_num_rows($result)) {
// it is true, so let's print the results to the browser
while($row = mysql_fetch_row($result))
{
print("$row[0]<br>"wink;
}
} else {
// false, no results
}

What if we want to find all email addresses that begin with the letter "a"?

$query = "SELECT email FROM email LIKE 'a%' ";
$result = mysql_query($query, $mysql_access);
if(mysql_num_rows($result)) {
// it is true, so let's print the results to the browser
while($row = mysql_fetch_row($result))
{
print("$row[0]<br>"wink;
}
} else {
// false, no results
}

How to UPDATE rows (change existing data) in a table in the mySQL database

Let's say we mispelled my email address when entering it into the script above. Instead of tdscripts we spelled it tdscipts.com (missing the "r"wink. Here is how we would build a query to change it.

$query = "UPDATE email set email='abc@123.com' ";
$query = " WHERE email='abc@123.com' ";
mysql_query($query, $mysql_access);

Pretty slick huh? Important note: If you omit the WHERE clause above then it would change every email address in the table to be the same, so be careful not to omit the WHERE clause which effectively singles out a specific row of data.

How to DELETE rows (remove existing data) in a table in the mySQL database

What if we want to remove a duplicate email address from the database? This is a little more tricky since as mentioned above, if you omit the WHERE clause mySQL will remove ALL instances. Fortunately when we CREATEd the email table we used a unique primary key known as ID. So you can remove the duplicate row by using the WHERE clause with the unique ID number. Let's say the duplicate email address is ID 2, here is how we'd construct the query to delete the duplicate email address from the table based on the unique id.

$query = "DELETE FROM email WHERE ID='2' ";
mysql_query($query, $mysql_access);

It is better of course to query the database first before adding data which might be the same as existing data, but this shows how to remove duplicates if should they exist. One last note about DELETE. It is a very powerful SQL function and can erase the entire contents of a table quite easily, so always make sure you use a WHERE clause with it.

How to ALTER (add columns to the existing table) a table in the mySQL database

Let's say we want to add a column to our email table which will datestamp when a new email is added or last updated. Let's use telnet again to do this. Use the following query:

ALTER TABLE email ADD last_update DATETIME;

Now check the rows by using the following query:

SHOW COLUMNS from email;

You will now need to modify your scripts when inserting data, as the new column "last_updated" is present and when using INSERT to add a new row the inserted data must match the fieldtypes. I have modified the script to add an email address now which will also datestamp it when it is added below. It only involves altering the query:

$query = "INSERT INTO email ";
$query .= "VALUES(0, '$email', SYSDATE() )";

Now SYSDATE() will be replaced with a date/time stamp using the server date/time when the email was last added. If you want to update the time just follow the instructions above to UPDATE and make it

set last_update=SYSDATE();

Commenting Your mySQL Tables

I am kind of a nut about keeping good notes in my code, so I like to use those comment tags -- at least in my working versions -- of scripts I write. I may remove some of these comments from public versions, but in my working versions I always keep good notes. When settiing up mySQL tables I like to put that information in between comments tags in PHP as follows:

<?

/* mySQL Table create
USE: tracks ratings at php-scripts.com

create table structure here,

a "test" SQL insert to the database

*/

I always include a test SQL insert statement to show how the data will be inserted into the rows if I was typing it directly into telnet. I am going to use PHP to insert, query, and delete data from the tables, but I want to make sure that I am making a valid insert from telnet, before moving onto creating the PHP code to interact with mySQL. You don't have to do the above step, but when you have problems later on, you can refer to this as a reference point for what each field name is and how to insert information into the table properly. It serves as a pretty decent guide.

Creating mySQL tables

Now let's create a table for the rating box used by php-scripts site. Currently the only thing we track is the vote number. However I would like to add a couple more fields to this. The first is the filename which is the 6 digit date, the second is the vote the user is registering, the third is the unique IP address, and lastly the date/time that they are voting. So basically we are taking all the files and breaking them down into one TABLE with the information. It will look like this

filename | vote | IP address | date/time of vote

This is quite an improvement in tracking information over just tracking the vote and recording in the appropriate file. It may not be apparent how useful this is right now, but it will in future diary entries when you see how we can query this data and present it in interesting ways. Here is how I would go about creating the above table using telnet:

CREATE TABLE rating
(
RATE_ID INT NOT NULL AUTO_INCREMENT,
filename CHAR(6),
vote CHAR(1),
ip_ad VARCHAR(16),
log_date DATETIME,
PRIMARY KEY (RATE_ID)
);
Re: Connecting To A Mysql Database Using Php by Nobody: 10:10pm On Jan 15, 2009
I was at first confused as to whether this was a tutorial or a question. I understand wat u are tryin to do, but u must take it slowly.
And i was wondering if your post will be better appreciated here or in webmasters - but i am sure these topics have been well covered in webmasters - and i am even wonderin if this was in demand.
And again - you are making too many posts at once - even i have a little difficulty in reading all those codes at once.
Re: Connecting To A Mysql Database Using Php by pintogen: 10:28am On Jan 20, 2009
I believe you have a programming knowledge and you have undergone training compared to others that does not know basic on this, so why dont you give yourself time and study what is posted here, instead of laying emphasis on unecessary issues. If you have any question you are free to ask, no questions is too much for me to answer
Re: Connecting To A Mysql Database Using Php by quadrillio(m): 4:13pm On Jan 20, 2009
pintogen:

I believe you have a programming knowledge and you have undergone training compared to others that does not know basic on this, so why dont you give yourself time and study what is posted here, instead of laying emphasis on unecessary issues. If you have any question you are free to ask, no questions is too much for me to answer

Na talk be that or trying to show off.
Re: Connecting To A Mysql Database Using Php by pintogen: 10:12am On Jan 21, 2009
Am not trying to show off, am not better off than anybody nor am i saying am a guru, what am trying to say is that you are free to study the programme and let me know if there is any additional thing to add or any questions to be answered
Re: Connecting To A Mysql Database Using Php by Nobody: 10:22pm On Jan 21, 2009
@pint, I was actually not tryin to diss you (this is not one of such days) - i doubt if there is anything u hav posted so far on NL that i dont already hav a good grasp of (right back at ya) - however i was going through your codes tryin to see if beginners will be able to follow what u are posting.
The codes are so much clutters - i think you should try to at least wrap your codes in
code tag
just so that readers will not be discouraged.
And of course, your readers will be more of learners.

By the way, you ask me if i hav any question to ask? I do not wish to embarrass anybody, so just let me be - and keep up the good work - i can see u hav been pasting a lot recently - keep at it - just ignore whatever we amateurs have to say if it suits you - na bad belly dey worry us? grin
Re: Connecting To A Mysql Database Using Php by pintogen: 9:44am On Jan 22, 2009
Thank you dhtml i so much appreciate what you have point out, is only what you cant defend that you get scared of, i can never be afraid to entertain any question from readers, that as to do with what have posted in here and i will be ready and happy to answer them all.

I like your recent post keep on doing the work is good and we expect more from you
Re: Connecting To A Mysql Database Using Php by Nobody: 1:19pm On Jan 23, 2009
@least you can figure out now that i am not opposing you as you felt initially - not my style to carry bad-belly for anyone - even if they deserve it.

And now that you have realized that - u should also realize that you have lots of viewers - and anytime you are posting or replying posts - u should keep it in mind that the posters may fall into different categories:
1. Those that know some of what you are posting and are ready to learn more.
2. Those that are probably on the same level as you,
3. Those that are far more advanced than you

So you can also expect one of the actions below:
1. Acceptance - those that appreciate you and wish to learn more (true learners).
2. Abuses - these are to be expected from those that are jealous either they are on same level as you or below and just beef u.
3. Assistance - usually from those at same level or higher than u who just point out a few things to you.

Your duties:
1. You must try to assess what level the posters/viewers and reply accordingly.
2. You must never get annoyed with anybody even if they say "you are stupid / you are outdated / you are wasting your time"

Reasons:
1. Lots of ppl appreciate what you are doing.
2. Some of the ppl that are regular developers (like me) - are just here to assess your skill level not to learn per se.
3. You can get jobs from learners, those on same level, and those above though your posts.

In totality, just be responsible and try your best to do what is right, overcome the obstacles and spend little time replying to those that
are purely beefing you, analyze every advice - take the good ones and skip the rubbish ones

I faced the same challenges and probably worse. as you are now when i started posting topics in this forum.
In fact lots of ppl were sending alot of obscene messages thru my website, luckily i did not give out my email address.

Keep up the good job!
Re: Connecting To A Mysql Database Using Php by pintogen: 4:19pm On Jan 23, 2009
Thank you dhtml i so much appreciate the advice

(1) (Reply)

Most Used Mysql Database Functions / ''no Row At Position 0'' When Retrieving Image From An Sql Database Using Vb.net / Connecting To A Database Using Visual Basic

Iphone Development / I Have A Very Import Question On Python Syntax / Free Camscan Download

(Go Up)

Sections: politics (1) business autos (1) jobs (1) career education (1) romance computers phones travel sports fashion health
religion celebs tv-movies music-radio literature webmasters programming techmarket

Links: (1) (2) (3) (4) (5) (6) (7) (8) (9) (10)

Nairaland - Copyright © 2005 - 2019 Oluwaseun Osewa. All rights reserved. See How To Advertise. 131
Disclaimer: Every Nairaland member is solely responsible for anything that he/she posts or uploads on Nairaland.