Page 1 of 1

How do I set a DB value as a variable in PHP?

Posted: Thu Aug 23, 2007 8:09 am
by danarashad
how do I set a DB value as a variable in PHP.
I am a coldfusion coder, but i am trying to learn PHP. in coldfusion
i'd write <cfset variable_name=queryname.column_name>
how do you do this in php. thanks for your help

Edit: Modified subject line to something more appropriate.
Please be sure to read the thread appropriately titled Before Post Read: General Posting Guidelines at the top of the forum before posting.
Following these guidelines really do help you get your questions answered faster.

Posted: Thu Aug 23, 2007 8:26 am
by aceconcepts
Hi,

Usually I'd declare the db in a seperate file and include it.

i.e.

Code: Select all

mysql_select_db ("db_name");
So, i guess you might be able to assign a variable with this value or similar.

Posted: Thu Aug 23, 2007 8:28 am
by Steve Mellor
Well, first of all you have to connect to the database. Now this depends somewhat on which database you're connecting to. I am using MYSQL in this example.

Code: Select all

$hostname = "localhost";
$database = "icon";
$username = "root";
$password = "password";
$myConnection = mysql_connect($hostname, $username, $password) or trigger_error(mysql_error(),E_USER_ERROR);
It's a good idea to store this in a separate file but for the time being lets assume it's all in the same script. Next we select the database using PHP's native MySQL commands.

Code: Select all

mysql_select_db($database, $myConnection);
You can see what I'm doing here. $database is the name of the database, taken from the first code snippet. $myConnection is the actual connection to your MySQL server.

The next thing you need to do is write a statement. This is in MySQLs own script so it's not PHP code at all. I'm going to put it in a variable called query and I will be selecting all records from a table called monkey.

Code: Select all

$query = "SELECT * FROM monkey ";
Now, that's very basic. There are tones of things you can do to extract data from your database and that's the real power behind PHP/MySQL applications.

Next we run the actual query and store the results in a variable.

Code: Select all

$results = mysql_query($query, $myConnection);
Now we pull the results into another variable that we can reference from the rest of the script.

Code: Select all

$row = mysql_fetch_assoc($results);
We are now at a point where you can reference the first record you've pulled out using the echo command and the name of the field. Lets say we want to display the monkey's name.

Code: Select all

echo $row['name'];
And that's the basics. There are tones of tutorials on this if you search google. Hopefully you can search for specific bits of code now if you need more help on it. You'll also need to look at loops to draw out all of the records in the database.

Hope this helps.

Posted: Thu Aug 23, 2007 8:29 am
by jason
Assuming you are using MySQL (but it generally works like this in other db libraries, too), you'd want to look here.

http://php.net/mysql_fetch_assoc

This will give you a good start.

Posted: Thu Aug 23, 2007 8:42 am
by Kieran Huggins
@Steve Mellor - what a good intro. Will have to bookmark that for when it's asked in the future!

Posted: Thu Aug 23, 2007 8:49 am
by Steve Mellor
Kieran Huggins wrote:@Steve Mellor - what a good intro. Will have to bookmark that for when it's asked in the future!
Why, thank you :D

Posted: Thu Aug 23, 2007 8:54 am
by Kieran Huggins
I'd just like to add that mysql_fetch_assoc() advances the pointer, and will also evaluate as false when it reaches the end. Because of this, you can use a while loop to iterate the result set like so:

Code: Select all

while($row = mysql_fetch_assoc($results)){
   echo $row['name'];
}

Posted: Thu Aug 23, 2007 8:59 am
by Steve Mellor
Yes, I should have put that in but I'm meant to be working ;)

Posted: Thu Aug 23, 2007 9:08 am
by superdezign
Steve Mellor wrote:Yes, I should have put that in but I'm meant to be working ;)
Aren't we all? ;)