Development PHP / MySQL Help

Im trying to pull information from a database by id, but I just cant get it to work. Heres the code Im trying to use. Ive only been playing with PHP for a few days so Im not even sure if I am heading in the right direction. Any help would be great.

PHP:

<?php

    include 'db.inc.php';

    $id = $_GET['id'];
    $author = @mysql_query("SELECT name, email FROM author WHERE id='$id'");
    if (!$author) {
        exit('<h1 class=content>Error fetching author details:' . mysql_error() . '</h1>');
        }
        $author = mysql_fetch_array($author);
        
        $name = $author['name'];
        $email = $author['email'];
        
        // Convert special characters for safe use as HTML attributes
        $name = htmlspecialchars($name);
        $email = htmlspecialchars($email);
       
       
       
        echo "$name | $email"; 
        
?>

 

echo "$name";
echo "$email";

Are you trying to put them together?

 

I dont know the exacts on how php internally handles variables so this might not matter but it appears your overwriting your result handler with the result?

try:

PHP:

<?php

    include 'db.inc.php';

    $id = $_GET['id'];
    $result = mysql_query("SELECT name, email FROM author WHERE id='$id'");
    if (!$result) {
        exit('<h1 class=content>Error fetching author details:' . mysql_error() . '</h1>');
        }
        $author = mysql_fetch_array($result);
    
        // Convert special characters for safe use as HTML attributes
        $name = htmlspecialchars($author['name']);
        $email = htmlspecialchars($author['email']);
       
       
       
        echo "$name | $email"; 
        
?>

Take all the @s out as well so you can see if your raising any errors, same if you have @ in your include file. There may be something wrong with actually connecting to the database...

 

Am I missing anything but your code looks the same as mine?

The db include file works as all my other pages that use the include have no problems what so ever. Just incase heres the db.inc.php

PHP:

<?php
$dbcnx = @mysql_connect ( 'localhost', '******', '*****');
if (!$dbcnx) {
    exit('<p>Unable to connect to the database at this time.</p>');
}

if (!@mysql_select_db('******_cms')) {
     exit('<p>Unable to located the CMS database at this time.</p>');
}
?>

* = edited out the content

 

PHP:

$author = @mysql_query("SELECT name, email FROM author WHERE id='$id'");
# to
$result = mysql_query("SELECT name, email FROM author WHERE id='$id'"); # ST8's code


PHP:

$author = mysql_fetch_array($author);
# to
$author = mysql_fetch_array($result); # ST8's code


 

still doesnt seem to work...

 

it is db.inc.php that should be included right? not db_inc.php?

fairly noobish but seemed odd so I thought I'd point it out.

 

Is the query returning any results?

 

Remove the @'s from

@mysql_connect and
@mysql_select_db

and see if you get any errors

@ = supresses errors

 

It will suppress PHP errors but will still allow the script to output the custom error messages via exit()

 

This may be dumb, but 9 out of 10 php errors for me are quotes.

Where you have "...where id = '$id'"
drop the single quotes. Try that.

 

Ok well I just rewrote the entire thing again and now it works...Not sure where my problem was but it now works...

Should have posted a few hours ago and maybe I could have solved this sooner

Also for those who have more experience with PHP and MySQL is this the best way to achive what Im trying to do or is there a different / better approach I could take?

Edit: Anybody care to explain what "Error prefroming query: Column: 'id' in field list is ambiguous" means.

 

column id doesn't exist, and yes there are 'better' ways to do what you're doing

 

Then why can one script call it (the one I just had the problems with, which now works) but this script cant.

PHP:

<?php
//Database Acess Granted Here
include 'db.inc.php';
        
$results = @mysql_query ('SELECT id, title, contenttext, discription, logo, name, email FROM content, author WHERE authorid = author.id');
if (!$results)
{
    exit('<h1 class=content>Error prefroming query: ' . mysql_error() . '</h1>');
}

while ($content = mysql_fetch_array($results))
{
    $id = $content['id'];
    $title = $content['title'];
    $text = $content['contenttext'];
    $discription = $content['discription'];
    $logo = $content['logo'];
    $name = $content['name'];
    $email = $content['email'];
    
    echo "<h1>$title</h1><span class=content><span class=bold>Posted by:</span></span><br /><a href='mailto:$email'>$name</a>" .
         "<h1 class=content><span class=bold>Content:</span><br />$text</h1>" .
         "<h1 class=content><span class=bold>Discription:</span><br />$discription</h1>" .
         "<h1 class=content><span class=bold>Logo:</span><br /><img src=$logo width=180 height=80 border=0 class=tn></h1><p>" .
         "<a href='editcontent.php?id=$id'>Edit</a> | <a href='deletecontent.php?id=$id'>Delete</a></span><p>" .
         "<img src=http://www.technexus.net/images/dot.gif width=100% height=1 border=0>";
}
?>

Im guessing the problem is somewhere in here: "$results = @mysql_query ('SELECT id, title, contenttext, discription, logo, name, email FROM content, author WHERE authorid = author.id');"

 

Ok, guessing here at your tables but you should be able to adapt this if i get it wrong:

Code:

       
SELECT a.id, a.title, a.contenttext, a.discription, a.logo, b.name, b.email FROM content AS a, author AS b WHERE a.authorid = b.id 

 

Yup, RTTs got it. To put it into a verbal description, when doing a SELECT from multiple tables you need specify the tables the fields are coming from.

So in your example above you'd need to do

Code:

SELECT author.id, content.title, etc etc FROM author, content etc

or alias them as in RTTs example. Personally, I think it's easier to read if you just specify the table names instead of aliasing (but it can make your queries a bit long).

 

Well I just cut the code out for now but later Ill need to add it back.

Basicly I was just trying to join the tables:

Content Table:
authorid
|
V
Author Table:
id
name
email

So when I call authorid 1 it would give me access to the author table's id 1's name and email.

Would your examples still work for such a function??

 

Yes, they're the same query, they've just specified the table-name with the fields.

This is a guess, but you've probably got a 'name' field in the content table as well, right? In which case, the mysql error would be something like "field name 'name' ambiguous in SQL statement" meaning that it's found two fields called name, one in each of the joint tables, and it doesn't know which one to get as you haven't specified which one you want. In order to do this, and so the SQL engine knows which one you want, you have to specify the table name before it, or use aliases, a la RTT's example.

Even if it's not the name field in my guess, it's just good practice to alias your tables and use them with fieldnames as it can save yourself a lot of headache.

 

I didn't see this mentioned yet so i'll throw it in. If you have common column names and are selecting 2 or more of them with the same name you can use AS to 'rename' what they are 'selected' as. Imagine you have two tables both with a 'name' column, and you need to use both:

Code:

SELECT a.name [b]AS firstname[/b], a.email, a.zipcode, b.name [b]AS secondname[/b], b.somethingelse FROM table1 AS a, table2 AS b WHERE a.uid = b.uid

will result in:

Code:

+-----------+-------+---------+------------+---------------+
| firstname | email | zipcode | secondname | somethingelse |
+-----------+-------+---------+------------+---------------+
| data      | data  | data    | data       | data          |
| data      | data  | data    | data       | data          |
+-----------+-------+---------+------------+---------------+

 

Going to give this a shot when I get out of class....

Now technomancer said something about the quries being long when your just naming tables instead of aliasing them. I would imagine this would effect performence no?