stumped : PHP/MYSQL results in wrong return

[camarosource]

For the life of me I can't understand what I did wrong.

I am building a script that displays a html list (using Rows) with information from fields from a database. In the script it compares the EMAIL address of the current loaded SELECTed database to a different database. IF the email exists in THAT database as well, then it tries to obtain the user id and transfer it as a variable to be displays on the html page.

The problem is, while it sucessfully compares to see if the email address exists in the other database.. HOWEVER the user_id rather than being to CORRECT one, it seems to be just resulting in Assending numbers rather than the correct one for that table that goes with the email address.

IE.

Database (A) - SOURCE database

Fields : EMAIL

Database (😎 --- COMPARING to see if the email in (A) is also created in (😎

Fields : user_email
Fields: user_id

The script first loads Database (A) and SELECTS the EMAIL and compares to see if the SAME email exists in DATABASE (😎. If it DOES then it SHOULD be grabbing the USER_ID (no caps) from DATABASE (😎. If email does NOT exist then it simply displays the HTML, with the row and other variables, and loops back until completely cycled through all the Database rows.

HOWEVER what it is doing is doing this:

Rather than say

[e-mail] (user_email) : 1 (user_id)
[e-mail] (user_email) : 4 (user_id)
[e-mail] (user_email) : 3 (user_id)
[e-mail] (user_email) : 2 (user_id)

it's resulting in like:

[e-mail] (user_email) : 1 (user_id)
[e-mail] (user_email) : 2 (user_id)
[e-mail] (user_email) : 3 (user_id)
[e-mail] (user_email) : 4 (user_id)

Notice the user_id is in assending order instead.

<?php


// --------------- Connecting to Database


// create connection
$conn_cal09 = mysql_connect("localhost","[hidden]_DATABASE_A","[hidden]")
or die(mysql_error());

$conn_gallery = mysql_connect("localhost","[hidden]_DATABASE_B","[hidden]")
or die(mysql_error());


// select database
$db_cal09 = mysql_select_db("[hidden]_DATABASE_A", $conn_cal09) or die(mysql_error());

$db_gallery = mysql_select_db("[hidden]_DATABASE_B", $conn_gallery) or die(mysql_error());


// create SQL
$sql_cal09 = "SELECT SUBMISSION_ID, FULL_NAME, EMAIL, CAMARO_YEAR, CAMARO_MODEL, SAID_TO_PURCHASE,
ACTUALLY_PURCHASED, WERE_PICS_UPLOADED, ADMIN_COMMENTS, GALLERY_CREATED, WERE_PICS_GOOD_ENOUGH,
PICS_TOO_SMALL, PICS_WRONG_ANGLE, PICS_CUT_OFF, PICS_OTHER, EMAILED_PICS_NOT_GOOD
FROM user_submissions ORDER BY SUBMISSION_ID";

$sql_gallery = "SELECT * FROM 4images_users";



//Execute SQL query
$sql_result_cal09 = mysql_query($sql_cal09,$conn_cal09) or die(mysql_error());

$sql_result_gallery = mysql_query($sql_gallery,$conn_gallery) or die(mysql_error());


// start rpo code list formatting
include("HTTP://www.[HIDDEN]/[HIDDEN].htm");

// Format list by row
while ($row_cal09 = mysql_fetch_array($sql_result_cal09)) {
$ID = $row_cal09["SUBMISSION_ID"];
$NAME = $row_cal09["FULL_NAME"];
$EMAIL = $row_cal09["EMAIL"];
$YEAR = $row_cal09["CAMARO_YEAR"];
$MODEL = $row_cal09["CAMARO_MODEL"]; 
$PREFERRED_MONTH = $row_cal09["PREFERRED_MONTH"];
$SAID_TO_PURCHASE = $row_cal09["SAID_TO_PURCHASE"];
$ACTUALLY_PURCHASED = $row_cal09["ACTUALLY_PURCHASED"];
$GALLERY_CREATED = $row_cal09["GALLERY_CREATED"];
$WERE_PICS_UPLOADED = $row_cal09["WERE_PICS_UPLOADED"];
$ADMIN_COMMENTS = $row_cal09["ADMIN_COMMENTS"];
$WERE_PICS_UPLOADED = $row_cal09["WERE_PICS_UPLOADED"];
$GALLERY = "http://";
$WERE_PICS_GOOD_ENOUGH = $row_cal09["WERE_PICS_GOOD_ENOUGH"];
$PICS_TOO_SMALL = $row_cal09["PICS_TOO_SMALL"];
$PICS_WRONG_ANGLE = $row_cal09["PICS_WRONG_ANGLE"];
$PICS_CUT_OFF = $row_cal09["PICS_CUT_OFF"];
$PICS_OTHER = $row_cal09["PICS_OTHER"];
$EMAILED_PICS_NOT_GOOD = $row_cal09["EMAILED_PICS_NOT_GOOD"];

//ECHO $EMAIL;


// Check 4images Camaro Gallery to see if each users EMAIL exists in the Camaro Gallery.
// If So, this means they DID create a gallery account.
  $checkuser = "SELECT * FROM 4images_users WHERE user_email= '$EMAIL'";
  $query = mysql_query($checkuser);

  if ( mysql_num_rows($query) >= 1 ) {
   echo $EMAIL;
   $GALLERY_CREATED = YES; 
    $row_email_yes = mysql_fetch_array($sql_result_gallery);
    $user_id = $row_email_yes["user_id"];
    Echo $user_id;
    $GALLERY = "http://www.[HIDDEN]/[HIDDEN].php?action=showprofile&user_id=$user_id";
    echo "$GALLERY<br>";

   } ELSE  {
   $GALLERY_CREATED = NO;
   $GALLERY = "http://"; 
  }


//Script to add "http://" to URL if does not exist. Otherwise URL has problems with href
//if(substr($GALLERY, 0, 7)!="http://") $GALLERY = "http://".$GALLERY;


echo "<tr>
          <td bgcolor=#EEEEEE align=center height=1>
          <b><font size=1 face=Arial>$ID</font></b></td>
          <td bgcolor=#EEEEEE align=center height=1>
          <b><font size=1 face=Arial>
<a href=http://www.[HIDDEN]/[HIDDEN].php?id=$ID>$NAME</a></font></b></td>
          <td bgcolor=#EEEEEE align=center height=1>
          <b>
   <font size=1 face=Arial>
   <a href=mailto:$EMAIL>Click</a></font></b></td>
          <td bgcolor=#EEEEEE align=center height=1>
          <b><font size=1 face=Arial>$YEAR</font></b></td>
          <td bgcolor=#EEEEEE align=center height=1>
          <b><font size=1 face=Arial>$MODEL</font></b></td>
          <td bgcolor=#EEEEEE align=center height=1>
          <b><font size=2 face=Arial>$SAID_TO_PURCHASE</font></b></td>
          <td bgcolor=#EEEEEE align=center height=1>
          <font size=4 color=#FF0000><b>$ACTUALLY_PURCHASED</b></font></td>
          <td bgcolor=#EEEEEE align=center height=1>
          <b><font face=Arial size=2>$GALLERY_CREATED</font></b></td>
          <td bgcolor=#EEEEEE align=center height=1>
          <b><font size=2 face=Arial>$WERE_PICS_UPLOADED</font></b></td>
          <td bgcolor=#EEEEEE align=center height=1>
    <b><font face=Arial size=2><a target=_blank href=$GALLERY>
    Click</a></font></b></td>
          <td bgcolor=#EEEEEE align=center height=1>
          <font size=1 face=Arial>$WERE_PICS_GOOD_ENOUGH</font></td>
          <td bgcolor=#EEEEEE align=center height=1>
          <font face=Arial size=1>$PICS_TOO_SMALL</font></td>
          <td bgcolor=#EEEEEE align=center height=1>
          <font face=Arial size=1>$PICS_WRONG_ANGLE</font></td>
          <td bgcolor=#EEEEEE align=center height=1>
          <font face=Arial size=1>$PICS_CUT_OFF</font></td>
          <td bgcolor=#EEEEEE align=center height=1>
          <font face=Arial size=1>$PICS_OTHER</font></td>
          <td bgcolor=#EEEEEE align=center height=1>
          <font face=Arial size=1>$EMAILED_PICS_NOT_GOOD</font></td>
          <td bgcolor=#EEEEEE align=center height=1>
          <b><font size=1 face=Arial>$ADMIN_COMMENTS</font></b></td>
        </tr>";
}
echo "</TABLE>";
?>

Completely Baffled! Help Thanks!

[halojoy]

Notice the user_id is in assending order instead.

I means probably,

either one of these:

1. that your search uses an INDEX KEY that is ORDERed by user_id
   or that the order of user_id follows some other index in this very ORDER

2. that the ORDER BY you have specified in your QUERY,
   in some way
   results in ordered by user_id (see point 1.)

If you want to see what orders / indexes are in your DB Tables
.. use your phpMyAdmin to have a look into tables effected

regars 🙂
halojoy