SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Enthusiast Chunks's Avatar
    Join Date
    May 2002
    Location
    Australia
    Posts
    46
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Help needed with a array & query

    hi all,

    i am working along in kevin yanks book (php & MySQL) and i am having probs with the array code below

    PHP Code:
    $query = @mysql_query("SELECT jokes.ID, jokeText, jokeDate, Name, Email
                            FROM jokes, authors WHERE AID=authors.id"
    );
    if(! 
    $query){
    echo(
    "<p>Query not performed.</p>");
    exit();
    }

    while(
    $array mysql_fetch_array($query)){
    $jokeID=$array["jokes.ID"];
    $authorsName=$array["Name"];
    $authorsEmail=$array["Email"];
    $joketext=$array["jokeText"];
    $jokeDate=$array["jokeDate"];
    echo(
    "<p>$joketext " "<p>" "<font color='CC0000'>" "Date Added " 
        
    "$jokeDate"</font>" "</p>" "<p>" .
        
    "Submitted by " "<a href='mailto:$authorsEmail'>" "$authorsName</a>" 
        
    "</p>" "<p>" "<a href='$PHP_SELF?deleteJoke=$jokeID'>" "</p>" 
        
    "<p>" "Delete this joke</a></p><br />");  

    the problem basically iam having is with the $jokeID variable it doesn't seem to work.... i know the query works because i checked that in MySQL front and it returns all the fields including the joke ID, but why isn't
    PHP Code:
    $jokeID=$array["jokes.ID"]; 
    working??

    thanks for you help in advance

    Regards Chunks

  2. #2
    SitePoint Enthusiast
    Join Date
    Dec 2001
    Location
    LA, California
    Posts
    62
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Chunks

    I'm not entirely sure of the theory behind it, but try putting this in your SELECT statement ::
    SELECT jokes.ID AS JID

    and then use $JID instead of $Jokes.ID.

    I'm pretty sure its because you can only reference the table.column syntax inside the SQL query.
    But like I say... I'm not sure of the reasons ...

    Cheers
    Marc

  3. #3
    SitePoint Enthusiast Chunks's Avatar
    Join Date
    May 2002
    Location
    Australia
    Posts
    46
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks for the reply but i don't think that was the solution

    i fiddle around with the code and got it working here is the solution below:

    PHP Code:
    $query = @mysql_query("SELECT jokes.ID, jokeText, jokeDate, Name, Email
                            FROM jokes, authors WHERE AID=authors.id"
    );
    if(! 
    $query){
    echo(
    "<p>Query not performed.</p>");
    exit();
    }

    while(
    $array mysql_fetch_array($query)){
    $jokeID=$array["ID"];
    $authorsName=$array["Name"];
    $authorsEmail=$array["Email"];
    $joketext=$array["jokeText"];
    $jokeDate=$array["jokeDate"];
    echo(
    "<p>$joketext " "<p>" "<font color='CC0000'>" "Date Added " 
        
    "$jokeDate"</font>" "</p>" "<p>" .
        
    "Submitted by " "<a href='mailto:$authorsEmail'>" "$authorsName</a>" 
        
    "</p>" "<p>" "<a href='$PHP_SELF?deleteJoke=$jokeID'>" "</p>" 
        
    "<p>" "Delete this joke</a></p><br />");  

    so instead of $jokeID=$array["jokes.ID"]; its is $jokeID=$array["ID"];

    well thats the solution.

    but iam still troubled.... what if i needed the id of two tables? what then? i couldn't use $array["ID"] because there are two tables with the column name ID in them.

    Eg....
    PHP Code:
    $query = @mysql_query("SELECT table1.ID, table2.ID jokeText, jokeDate, Name, Email
                            FROM jokes, authors WHERE AID=authors.id"
    );

    if(
    $row=mysql_fetech_array($query)){
    $table1=$row["ID"];
    $table2=$row["ID"];
    echo(
    "$table1"$table2");

    this wouldn't work would it? So whats the solution?
    Can a PHP guru help me to understand this concept??

    Regards Chunks

  4. #4
    SitePoint Enthusiast
    Join Date
    Dec 2001
    Location
    LA, California
    Posts
    62
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ah, yes you are correct. But then so am I . I believe my solution will work for both your issues.

    Just do ::

    PHP Code:
    $query = @mysql_query("SELECT table1.ID AS ID1, table2.ID AS ID2, jokeText, jokeDate, Name, Email
                            FROM jokes, authors WHERE AID=authors.id"
    );

    if(
    $row=mysql_fetech_array($query)){
    $table1=$row["ID1"];
    $table2=$row["ID2"];
    echo(
    "$table1"$table2");

    and it will work just fine... really, trust me on this one.
    Except of course that in my previous example, there shouldn't have been a $ in front of the JID... doh! .

    Cheers
    Marc

  5. #5
    SitePoint Wizard silver trophy redemption's Avatar
    Join Date
    Sep 2001
    Location
    Singapore
    Posts
    5,269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    that's what aliases are for...

    taking from Danny's example, with this query:
    Code:
    SELECT 
      jokes.ID AS ID1, authors.ID AS ID2
    FROM
      jokes, authors
    WHERE
      AID=authors.id
    you can access them as $row['ID1'] and $row['ID2']... use an alias whenever there is any doubt or ambiguity over which table your field should come from...

  6. #6
    SitePoint Addict eddiembabaali's Avatar
    Join Date
    Mar 2001
    Location
    USA, Philadelphia
    Posts
    206
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Extract from manual :

    http://www.php.net/manual/en/functio...etch-array.php

    If two or more columns of the result have the same field names, the last column will take precedence. To access the other column(s) of the same name, you must use the numeric index of the column or make an alias for the column. For aliased columns, you cannot access the contents with the original column name (by using 'field' in this example).

    Example 1. Query with duplicate field names

    select table1.field as foo table2.field as bar from table1, table2


    Good luck
    cd pub \r; more beer

    Eddie

  7. #7
    SitePoint Enthusiast Chunks's Avatar
    Join Date
    May 2002
    Location
    Australia
    Posts
    46
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thanks all!!

    Thanks for the replies,

    i think i finally get it....

    i didn't realize there was a "as" command in SQL to create an "alias", thanks to DannyTCOTW for pointing that out, i kinda missed that point in your first reply, but the when i saw the code with the modified SQL statement the hammer fell......cool...tjis will get a lot of use

    Thanks once again!!
    Regards
    Chunks


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •