#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2000
    Location
    Sacramento
    Posts
    50
    Rep Power
    14
    Forgive me for the long post.

    I have a script that pulls information from a table. This table contains foreign id keys for other tables. This code works just fine, but pulls id numbers. I would like to make a change to this file to convert the id numbers into their matching names. I have tried to use this type of code:

    // create connection
    $connection = mysql_connect(localhost, nwpuser, nwp384fuel3)
    or die("Couldn't make connection.");

    // select database
    $db = mysql_select_db("NWP", $connection)
    or die("Couldn't select database.");


    // create SQL statement

    $sql = "SELECT NAME

    FROM DELIVERY

    WHERE ID= '$F_DELIVERY_ID'";

    // execute SQL query and get result
    $sql_result = mysql_query($sql,$connection)
    or die("Couldn't execute query.");

    // put data into drop-down list box
    while ($row = mysql_fetch_array($sql_result)) {

    $delivery_name = $row["NAME"];


    within the file below to convert the id #s into names, but I get errors. Any help would be greatly appreciated!


    The file:

    <?

    /create connection

    $connection = mysql_connect (localhost, ####, ###) or die ("Couldn't connect to server.");


    // select database


    $db = mysql_select_db (DB, $connection) or die ("Couldn't select database.");


    // create SQL statement


    $sql = "SELECT ID, CREATED, F_LOCATION_ID, F_CUSTOMER_ID, F_FUEL_TYPE_ID, F_DELIVERY_ID, VOLUME, PRICE
    FROM BID
    WHERE REVIEWED = 0";

    // execute SQL query and get result

    $sql_result = mysql_query($sql,$connection) or die ("Couldn't execute query.");


    //start results formatting

    echo "<br><br><div align=center>";

    echo "<table border=0 cellpadding=3 cellspacing=3 width='90%'>";

    echo "<tr><td colspan=9><font face='verdana,arial,helvetica' size='+1' color='#000000'><b>New Bids</b></font></td></tr>";


    // format results by row


    while ($row = mysql_fetch_array($sql_result)) {


    $CREATED = $row["CREATED"];
    $ID = $row["ID"];
    $F_CUSTOMER_ID = $row["F_CUSTOMER_ID"];
    $F_LOCATION_ID = $row["F_LOCATION_ID"];
    $F_FUEL_TYPE_ID = $row["F_FUEL_TYPE_ID"];
    $F_DELIVERY_ID = $row["F_DELIVERY_ID"];
    $VOLUME= $row["VOLUME"];
    $PRICE = $row["PRICE"];
    $REVIEWED = $row["REVIEWED"];
    $ACCEPTED = $row["ACCEPTED"];
    $COMMENTS = $row["COMMENTS"];

    echo "<tr bgcolor='#999999'>

    <td><font face='verdana,arial,helvetica' size='-2' color='#000000'><b>Date:</b></font></td>

    <td><font face='verdana,arial,helvetica' size='-2' color='#000000'><b> Bid Number:</b></font></td>

    <td><font face='verdana,arial,helvetica' size='-2' color='#000000'><b>Customer:</b></font></td>

    <td><font face='verdana,arial,helvetica' size='-2' color='#000000'><b>Location:</b></font></td>

    <td><font face='verdana,arial,helvetica' size='-2' color='#000000'><b>Fuel Type:</b></font></td>

    <td><font face='verdana,arial,helvetica' size='-2' color='#000000'><b>Delivery Type:</b></font></td>

    <td><font face='verdana,arial,helvetica' size='-2' color='#000000'><b>Volume:</b></font></td>

    <td><font face='verdana,arial,helvetica' size='-2' color='#000000'><b>Price:</b></font></td>

    <td><font face='verdana,arial,helvetica' size='-2' color='#000000'><b>Accept:</b></font></td>

    <td><font face='verdana,arial,helvetica' size='-2' color='#000000'><b>Decline:</b></font></td>

    </tr>";


    echo "<tr>

    <td><font face='verdana,arial,helvetica' size='-2' color='#000000'>$CREATED</font></td>

    <td><font face='verdana,arial,helvetica' size='-2' color='#000000'>$ID</font></td>

    <td><font face='verdana,arial,helvetica' size='-2' color='#000000'>$F_CUSTOMER_ID, $customer_name</font></td>

    <td><font face='verdana,arial,helvetica' size='-2' color='#000000'>$F_LOCATION_ID</font></td>

    <td><font face='verdana,arial,helvetica' size='-2' color='#000000'>$F_FUEL_TYPE_ID</font></td>

    <td><font face='verdana,arial,helvetica' size='-2' color='#000000'>$F_DELIVERY_ID, $delivery_name</font></td>

    <td><font face='verdana,arial,helvetica' size='-2' color='#000000'>$VOLUME</font></td>

    <td><font face='verdana,arial,helvetica' size='-2' color='#000000'>$PRICE</font></td>


    <td>

    <form action= method=GET>
    <input type=hidden name='ID' size=25 maxlength=25>
    <input type=submit value=Accept>
    </form>

    </td>

    <td>

    <form action= method=GET>
    <input type=hidden name='ID' size=25 maxlength=25>
    <input type=submit value=Reject>
    </form>

    </td>

    </tr>";

    }


    echo "</table>";


    echo "</div>";

    include("footer.inc");


    //free resources and close connection


    mysql_free_result($sql_result);
    mysql_close($connection);


    ?>
  2. #2
  3. .Net Developer
    Devshed Novice (500 - 999 posts)

    Join Date
    Feb 2000
    Location
    London
    Posts
    987
    Rep Power
    15

    <<I would like to make a change to this file to convert the id numbers into their matching names. >>


    Issue a single sql query..

    $sql = "SELECT D.NAME,B.ID, B.CREATED, B.F_LOCATION_ID, B.F_CUSTOMER_ID, B.F_FUEL_TYPE_ID, B.F_DELIVERY_ID, B.VOLUME, B.PRICE FROM BID as B,DELIVERY as D
    WHERE B.REVIEWED = 0 AND D.ID=B.F_DELIVERY_ID";

    If you can do this then you can save half of your work...

    Good Luck!!

    ------------------
    SR -
    webshiju.com

    "The fear of the LORD is the beginning of knowledge..."

    [This message has been edited by Shiju Rajan (edited August 10, 2000).]
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2000
    Location
    Sacramento
    Posts
    50
    Rep Power
    14
    interesting. Could you briefly explain the B. etc or D. etc..??

    Sorry, I'm very new to this.
  6. #4
  7. .Net Developer
    Devshed Novice (500 - 999 posts)

    Join Date
    Feb 2000
    Location
    London
    Posts
    987
    Rep Power
    15

    <<
    Could you briefly explain the B. etc or D. etc..??
    >>


    yea,We are going to get data from two tables BID and DELIVERY.So we should assagin two alias name to both the tables for getting values from those tables.here i gave BID as B and DELIVERY as D.when i say B.ID means it will get the id field value from table BID.Above we are comparing Delivery ID= bid F_DELIVERY_ID and get the corresponding details from both the tables.


    please check it out mysql manual you get more details from that.




    ------------------
    SR -
    webshiju.com

    "The fear of the LORD is the beginning of knowledge..."
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2000
    Location
    Sacramento
    Posts
    50
    Rep Power
    14
    Thank you. Can you show me how I would, using your example, format the results by row. ie:

    while ($row = mysql_fetch_array($sql_result))
    {

    $??? = $row["???"];

    Thank you. You mentioned you have a book? Where can I buy a copy?
  10. #6
  11. .Net Developer
    Devshed Novice (500 - 999 posts)

    Join Date
    Feb 2000
    Location
    London
    Posts
    987
    Rep Power
    15

    <<
    while ($row = mysql_fetch_array($sql_result))
    {

    $??? = $row["???"];
    >>



    just place the field names to the $row array.

    while ($row = mysql_fetch_array($sql_result))
    {

    $name = $row["NAME"];
    $created = $row["CREATED"];
    .....
    .....

    }




    ------------------
    SR -
    webshiju.com

    "The fear of the LORD is the beginning of knowledge..."
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2000
    Location
    Sacramento
    Posts
    50
    Rep Power
    14
    I tried this, but it won't execute. Any thoughts?

    $sql = "SELECT D.NAME, FL.NAME, FC.NAME, FT.NAME, B.ID, B.CREATED, B.F_LOCATION_ID, B.F_CUSTOMER_ID, B.F_FUEL_TYPE_ID, B.F_DELIVERY_ID, B.VOLUME, B.PRICE

    FROM BID as B, DELIVERY as D, LOCATION as FL, CUSTOMER as FC, FUEL_TYPE as FT

    WHERE B.REVIEWED = 0 AND D.ID=B.F_DELIVERY_ID, FL.ID=B.F_LOCATION_ID, FC.ID=B.F_CUSTOMER_ID, FT.ID=B.F_FUEL_TYPE_ID";


    // execute SQL query and get result

    $sql_result = mysql_query($sql,$connection) or die ("Couldn't execute query.");


    //start results formatting

    echo "<br><div align=center>";

    echo "<table border=0 cellpadding=3 cellspacing=3 width='90%'>";

    echo "<tr><td colspan=9><font face='verdana,arial,helvetica' size='+1' color='#000000'><b>New Bids</b></font></td></tr>";


    // format results by row


    while ($row = mysql_fetch_array($sql_result))

    {

    $CREATED = $row["CREATED"];
    $ID = $row["ID"];
    $F_CUSTOMER_ID = $row["NAME"];
    $F_LOCATION_ID = $row["NAME"];
    $F_FUEL_TYPE_ID = $row["NAME"];
    $F_DELIVERY_ID = $row["NAME"];
    $VOLUME= $row["VOLUME"];
    $PRICE = $row["PRICE"];
    $REVIEWED = $row["REVIEWED"];
    $ACCEPTED = $row["ACCEPTED"];

  14. #8
  15. .Net Developer
    Devshed Novice (500 - 999 posts)

    Join Date
    Feb 2000
    Location
    London
    Posts
    987
    Rep Power
    15

    <<
    $sql = "SELECT D.NAME, FL.NAME, FC.NAME, FT.NAME, B.ID, B.CREATED, B.F_LOCATION_ID, B.F_CUSTOMER_ID, B.F_FUEL_TYPE_ID, B.F_DELIVERY_ID, B.VOLUME, B.PRICE
    FROM BID as B, DELIVERY as D, LOCATION as FL, CUSTOMER as FC, FUEL_TYPE as FT
    WHERE B.REVIEWED = 0 AND .ID=B.F_DELIVERY_ID, FL.ID=B.F_LOCATION_ID, FC.ID=B.F_CUSTOMER_ID, FT.ID=B.F_FUEL_TYPE_ID";
    >>


    Actually your sql statement is wrong.
    ----------------------

    you should give "AND" in between where conditions..

    try the following...

    $sql = "SELECT D.NAME, FL.NAME, FC.NAME, FT.NAME, B.ID, B.CREATED, B.F_LOCATION_ID, B.F_CUSTOMER_ID, B.F_FUEL_TYPE_ID, B.F_DELIVERY_ID, B.VOLUME, B.PRICE

    FROM BID as B, DELIVERY as D, LOCATION as FL, CUSTOMER as FC, FUEL_TYPE as FT
    WHERE B.REVIEWED = 0 AND D.ID=B.F_DELIVERY_ID AND FL.ID=B.F_LOCATION_ID AND FC.ID=B.F_CUSTOMER_ID AND FT.ID=B.F_FUEL_TYPE_ID";


    ------------------
    SR -
    webshiju.com

    "The fear of the LORD is the beginning of knowledge..."

    [This message has been edited by Shiju Rajan (edited August 11, 2000).]
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2000
    Location
    Sacramento
    Posts
    50
    Rep Power
    14
    Thank you very much! I appreciate the time you've taken to help.

  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2000
    Location
    Sacramento
    Posts
    50
    Rep Power
    14
    Hi,

    I've tried the code and it works fine with one minor problem.

    $CREATED = $row["CREATED"];
    $ID = $row["ID"];
    $F_CUSTOMER_ID = $row["NAME"];
    $F_LOCATION_ID = $row["NAME"];
    $F_FUEL_TYPE_ID = $row["NAME"];
    $F_DELIVERY_ID = $row["NAME"];
    $VOLUME= $row["VOLUME"];
    $PRICE = $row["PRICE"];
    $REVIEWED = $row["REVIEWED"];
    $ACCEPTED = $row["ACCEPTED"];


    Above is the example. If only one of the results have "NAME" then it works fine, but since those fields are called NAME in the they have to display, but it doesn't work. How can I get around this? Any thoughts?

    Thanks!
  20. #11
  21. .Net Developer
    Devshed Novice (500 - 999 posts)

    Join Date
    Feb 2000
    Location
    London
    Posts
    987
    Rep Power
    15
    Ok!!!
    i understand your problem.use coulmn index insted of coulmn names

    eg: $row[0],$row[1],$row[2],$row[3]..etc

    //coulmn index starts from 0.


    <<
    SELECT D.NAME, FL.NAME, FC.NAME, FT.NAME, B.ID, B.CREATED, B.F_LOCATION_ID, B.F_CUSTOMER_ID, B.F_FUEL_TYPE_ID, B.F_DELIVERY_ID, B.VOLUME, B.PRICE
    >>


    Here use coulmn index like this..
    $row[0]
    //D.NAME

    $row[1]
    //FL.NAME

    $row[2]
    //FC.NAME

    $row[3]
    //FT.NAME


    ....
    ....




    ------------------
    SR -
    webshiju.com
    www.jobxyz.com-IT Career Portal
    ezipindia.com--WebStudio


    "The fear of the LORD is the beginning of knowledge..."
  22. #12
  23. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2000
    Location
    Sacramento
    Posts
    50
    Rep Power
    14
    Hi,

    Sorry to keep posting on this issue. I tried it and it seems as though the colunm index is giving me problems:


    $sql = "SELECT
    D.NAME,
    L.NAME,
    C.NAME,
    FT.NAME,
    B.ID,
    B.CREATED,
    B.F_LOCATION_ID,
    B.F_CUSTOMER_ID,
    B.F_FUEL_TYPE_ID,
    B.F_DELIVERY_ID,
    B.VOLUME,
    B.PRICE

    FROM

    BID as B,
    DELIVERY as D,
    LOCATION as L,
    CUSTOMER as C,
    FUEL_TYPE as FT


    WHERE

    B.REVIEWED = 0
    AND D.ID=B.F_DELIVERY_ID
    AND L.ID=B.F_LOCATION_ID
    AND C.ID=B.F_CUSTOMER_ID
    AND FT.ID=B.F_FUEL_TYPE_ID

    ";


    // execute SQL query and get result

    $sql_result = mysql_query($sql,$connection) or die ("Couldn't execute query.");


    //start results formatting

    echo "<br><div align=center>";

    echo "<table border=0 cellpadding=3 cellspacing=3 width='90%'>";

    echo "<tr><td colspan=9><font face='verdana,arial,helvetica' size='+1' color='#000000'><b>New Bids</b></font></td></tr>";


    // format results by row


    while ($row = mysql_fetch_array($sql_result))


    {


    $row[0]
    //D.NAME

    $row[1]
    //L.NAME

    $row[2]
    //C.NAME

    $row[3]
    //FT.NAME

    $row[4]
    //ID

    $row[5]
    //B.CREATED

    $row[6]
    //B.VOLUME

    $row[7]
    //B.PRICE

    echo "<tr bgcolor='#999999'>

    <td><font face='verdana,arial,helvetica' size='-2' color='#000000'><b>Date:</b></font></td>

    <td><font face='verdana,arial,helvetica' size='-2' color='#000000'><b> Bid Number:</b></font></td>

    <td><font face='verdana,arial,helvetica' size='-2' color='#000000'><b>Customer:</b></font></td>

    <td><font face='verdana,arial,helvetica' size='-2' color='#000000'><b>Location:</b></font></td>

    <td><font face='verdana,arial,helvetica' size='-2' color='#000000'><b>Fuel Type:</b></font></td>

    <td><font face='verdana,arial,helvetica' size='-2' color='#000000'><b>Delivery Type:</b></font></td>

    <td><font face='verdana,arial,helvetica' size='-2' color='#000000'><b>Volume:</b></font></td>

    <td><font face='verdana,arial,helvetica' size='-2' color='#000000'><b>Price:</b></font></td>

    <td><font face='verdana,arial,helvetica' size='-2' color='#000000'><b>Accept:</b></font></td>

    <td><font face='verdana,arial,helvetica' size='-2' color='#000000'><b>Decline:</b></font></td>

    </tr>";


    echo "<tr>

    <td><font face='verdana,arial,helvetica' size='-2' color='#000000'>$5</font></td>

    <td><font face='verdana,arial,helvetica' size='-2' color='#000000'>$4</font></td>

    <td><font face='verdana,arial,helvetica' size='-2' color='#000000'>$2</font></td>

    <td><font face='verdana,arial,helvetica' size='-2' color='#000000'>$1</font></td>

    <td><font face='verdana,arial,helvetica' size='-2' color='#000000'>$3</font></td>

    <td><font face='verdana,arial,helvetica' size='-2' color='#000000'>$0</font></td>

    <td><font face='verdana,arial,helvetica' size='-2' color='#000000'>$6</font></td>

    <td><font face='verdana,arial,helvetica' size='-2' color='#000000'>$7</font></td>
  24. #13
  25. .Net Developer
    Devshed Novice (500 - 999 posts)

    Join Date
    Feb 2000
    Location
    London
    Posts
    987
    Rep Power
    15

    <<
    while ($row = mysql_fetch_array($sql_result))


    {


    $row[0]
    //D.NAME

    $row[1]
    //L.NAME

    $row[2]
    //C.NAME

    $row[3]
    //FT.NAME

    $row[4]
    //ID

    $row[5]
    //B.CREATED

    $row[6]
    //B.VOLUME

    $row[7]
    //B.PRICE

    echo "<tr bgcolor='#999999'>

    <td><font face='verdana,arial,helvetica' size='-2' color='#000000'><b>Date:</b></font></td>

    <td><font face='verdana,arial,helvetica' size='-2' color='#000000'><b> Bid Number:</b></font></td>

    <td><font face='verdana,arial,helvetica' size='-2' color='#000000'><b>Customer:</b></font></td>

    <td><font face='verdana,arial,helvetica' size='-2' color='#000000'><b>Location:</b></font></td>

    <td><font face='verdana,arial,helvetica' size='-2' color='#000000'><b>Fuel Type:</b></font></td>

    <td><font face='verdana,arial,helvetica' size='-2' color='#000000'><b>Delivery Type:</b></font></td>

    <td><font face='verdana,arial,helvetica' size='-2' color='#000000'><b>Volume:</b></font></td>

    <td><font face='verdana,arial,helvetica' size='-2' color='#000000'><b>Price:</b></font></td>

    <td><font face='verdana,arial,helvetica' size='-2' color='#000000'><b>Accept:</b></font></td>

    <td><font face='verdana,arial,helvetica' size='-2' color='#000000'><b>Decline:</b></font></td>

    </tr>";


    echo "<tr>

    <td><font face='verdana,arial,helvetica' size='-2' color='#000000'>$5</font></td>

    <td><font face='verdana,arial,helvetica' size='-2' color='#000000'>$4</font></td>

    <td><font face='verdana,arial,helvetica' size='-2' color='#000000'>$2</font></td>

    <td><font face='verdana,arial,helvetica' size='-2' color='#000000'>$1</font></td>

    <td><font face='verdana,arial,helvetica' size='-2' color='#000000'>$3</font></td>

    <td><font face='verdana,arial,helvetica' size='-2' color='#000000'>$0</font></td>

    <td><font face='verdana,arial,helvetica' size='-2' color='#000000'>$6</font></td>

    <td><font face='verdana,arial,helvetica' size='-2' color='#000000'>$7</font></td>
    >>


    -------
    -------
    just try to print the values like following...

    while ($row = mysql_fetch_array($sql_result))
    {
    echo $row[0];
    //D.NAME

    echo $row[1];
    //L.NAME

    echo $row[2];
    //C.NAME

    echo $row[3];
    //FT.NAME

    echo $row[4];
    //ID

    echo $row[5];
    //B.CREATED

    echo $row[6];
    //B.VOLUME
    echo $row[7];
    //B.PRICE
    }


    It should work with out any problem..

    ---------------------

    <<
    $sql = "SELECT
    D.NAME,
    L.NAME,
    C.NAME,
    FT.NAME,
    B.ID,
    B.CREATED,
    B.F_LOCATION_ID,
    B.F_CUSTOMER_ID,
    B.F_FUEL_TYPE_ID,
    B.F_DELIVERY_ID,
    B.VOLUME,
    B.PRICE

    FROM

    BID as B,
    DELIVERY as D,
    LOCATION as L,
    CUSTOMER as C,
    FUEL_TYPE as FT ...
    ....
    ...
    >>



    ----------------------
    Or you can try one more option here.
    use a sql statement like following..
    ------------

    $sql = "SELECT
    D.NAME as D_NAME,
    L.NAME as L_NAME,
    C.NAME as C_NAME,
    FT.NAME as FT_NAME,
    B.ID as B_ID,
    B.CREATED as B_CREATED,
    B.F_LOCATION_ID as B_F_LOCATION_ID,
    B.F_CUSTOMER_ID as B_F_CUSTOMER_ID,
    B.F_FUEL_TYPE_ID as B_F_FUEL_TYPE_ID,
    B.F_DELIVERY_ID as B_F_DELIVERY_ID,
    B.VOLUME as B_VOLUME,
    B.PRICE as B_PRICE

    FROM

    BID as B,
    DELIVERY as D,
    LOCATION as L,
    CUSTOMER as C,
    FUEL_TYPE as FT

    WHERE your conditions...

    here you are using the column alias names so can retrieve the value using that alias name..

    while ($row = mysql_fetch_array($sql_result))
    {
    echo $row["D_NAME"];
    //D.NAME

    echo $row["L_NAME"];
    //L.NAME

    echo $row["C_NAME"];
    //C.NAME

    echo $row["FT_NAME"];
    //FT.NAME

    echo $row["B_ID"];
    //ID

    echo $row["B_CREATED"];
    //B.CREATED

    echo $row["B_VOLUME"];
    //B.VOLUME
    echo $row["B_PRICE"];
    //B.PRICE
    }



    <<
    Sorry to keep posting on this issue
    >>


    Feel free and post any problems here..


    Good Luck!!!


    ------------------
    SR -
    webshiju.com
    www.jobxyz.com-IT Career Portal
    ezipindia.com--WebStudio


    "The fear of the LORD is the beginning of knowledge..."

    [This message has been edited by Shiju Rajan (edited August 17, 2000).]
  26. #14
  27. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2000
    Location
    Sacramento
    Posts
    50
    Rep Power
    14
    It works fine now! Thank you very much!
  28. #15
  29. .Net Developer
    Devshed Novice (500 - 999 posts)

    Join Date
    Feb 2000
    Location
    London
    Posts
    987
    Rep Power
    15

    You are welcome tbalanco!!


    ------------------
    SR -
    webshiju.com
    www.jobxyz.com-IT Career Portal
    ezipindia.com--WebStudio


    "The fear of the LORD is the beginning of knowledge..."

Similar Threads

  1. Replies: 3
    Last Post: February 11th, 2004, 08:30 AM
  2. Will table size affect data retrival time?
    By abones in forum Oracle Development
    Replies: 1
    Last Post: February 10th, 2004, 08:55 AM
  3. Replies: 0
    Last Post: January 30th, 2004, 02:18 PM
  4. how to keep table on top, not centered??
    By vinyl in forum HTML Programming
    Replies: 2
    Last Post: January 20th, 2004, 06:19 PM
  5. How to Extract data from html table?
    By zlozi in forum PHP Development
    Replies: 4
    Last Post: January 17th, 2004, 07:43 AM

IMN logo majestic logo threadwatch logo seochat tools logo