Extracting Information from MySQL with PHP

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Tom
Forum Newbie
Posts: 20
Joined: Sat Oct 05, 2002 5:24 pm
Location: Southwest FL

Extracting Information from MySQL with PHP

Post by Tom »

Alright.
So I'm this PHP newbie who's just now getting into MySQL and the whole database deal. I think I've gotten modifying the database via the command prompt and adding to the database via PHP down pat, but it's getting information out and displaying it that really gets me. I see scripts i can just copy and paste, but I'd like to know what they actually mean. Here's a script i copy and pasted that works fine, but I'd like to know what it means.

Code: Select all

<?php
        // Loop the results 
        // Note, results are returned as an 
        // Array - we cannot just print it out. 
    while($row = @mysql_fetch_array($result)) { 
            $id = $rowї"id"]; 
            $name = $rowї"name"]; 
            $description = $rowї"email"]; 
?>
Alright. So my first question is what's with the "[ ]" brackets? I've never seen those before now and i have no idea when/what they're used for.
Next, when i echo out the variable "description" (Which really is for email..I just edited the script), it displays every single email in the entire table. To just display the desired email, what would i do? I have a faint idea here..

Code: Select all

<?php
$query = mysql_query("SELECT from userinfo where email='$search'");
?>
But beyond that I'm clueless.
I know these questions are very "newbie-ish", but hey, how else will I learn? :?:

Take it easy guys, - Tom
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

Alright. So my first question is what's with the "[ ]" brackets? I've never seen those before now and i have no idea when/what they're used for.
they reference an array entry, http://www.php.net/manual/en/language.types.array.php

for the next question I suggest a (my)sql-tutorial
http://www.google.de/search?q=sql+tutorial
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

Tom
Forum Newbie
Posts: 20
Joined: Sat Oct 05, 2002 5:24 pm
Location: Southwest FL

Hmm..

Post by Tom »

So I've figured out how to extract an entire table's worth of information, but how do i extract a specified portion of the table?

I tried this..

Code: Select all

mysql_connect("localhost","Tom","tom") or die("Can''t connect to MySQL");
mysql_select_db("users") or die("Can''t connect to MySQL");
$Query = mysql_query("SELECT FROM userinfo WHERE name='Tom Stearns'");

 while($row = mysql_fetch_array($Query)) {
  $name = $rowїname];
  $email = $rowїemail];
  $location = $rowїlocation];
  
  echo "$name, $email, $location"."<br>";
  &#125;

mysql_close();
I get some kind of invalid mysql resource or something. Oh yeah..Is a loop a must in displaying data or is there another way?

Take it easy, -Tom
User avatar
phpScott
DevNet Resident
Posts: 1206
Joined: Wed Oct 09, 2002 6:51 pm
Location: Keele, U.K.

possible solution

Post by phpScott »

In your select statement you are saying

Code: Select all

&lt;?php
$Query = mysql_query("SELECT FROM userinfo WHERE name='Tom Stearns'"); 
?&gt;
try telling the select statement what your want to select what you want to return like

Code: Select all

&lt;?php
$Query = mysql_query("SELECT * FROM userinfo WHERE name='Tom Stearns'"); 
?&gt;
which will return everything or try

Code: Select all

&lt;?php
$Query = mysql_query("SELECT name, email, location FROM userinfo WHERE name='Tom Stearns'"); 
?&gt;
which will return the 3 fields you are looking for.

phpScott
p.s. I tried to run a simillar query to yours and it came back with an error as well, but the others did not.
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

A good way to debug your MySQL queries is to do something like this:

Code: Select all

$sql = "SELECT name, email, location FROM userinfo WHERE name='Tom Stearns'";
$result = mysql_query($sql) or die(mysql_error().'&lt;p&gt;'.$sql.'&lt;/p&gt;');
Using mysql_error() means you get a better error message than PHP's generic one's for dealing with database problems and by having the SQL statement in its own variable you can echo it out to see what it looks like in case you need to check that variables are being passed to it properly and just to check for typos.

Mac
User avatar
Crashin
Forum Contributor
Posts: 223
Joined: Mon May 06, 2002 3:42 pm
Location: Colorado

Post by Crashin »

Regarding your loop question:
I get some kind of invalid mysql resource or something. Oh yeah..Is a loop a must in displaying data or is there another way?
It depends upon what you want to do with the information. If you want to pull a single item from your query, you can do this:

Code: Select all

$row = mysql_fetch_array($Query);
echo $row&#1111;name];
Or, whatever you want to do with the information. The loop is used when you want to display ALL of the records pulled out via your query. The loop wouldn't do you much good in your example unless you had more than one record with an associated name of "Tom Stearns."

Hope that clears things up a little. :)
Post Reply