PHP + MySQL help

S

sigmend

[H]ard|Gawd
Joined
Aug 6, 2003
Messages
1,303
Hey all,
Ill get straight to the point.
When run this php script, I get a table seven rows high, but only data in the first row.
I am not sure what is wrong, but I think mysql_fetch_row does not do what I want. I don't know what to use in its place.
Or maybe the problem is unrelated, I don't know

I am still learning PHP.. I don't totally know what I am doing yet
Also, if you have optimizations, go ahead and post them
PHP: 
//Get how manny rows there are.
$Number_result =  query("SELECT * from checkin", $WHD_DB);
$Number = mysql_num_rows($Number_result);

//Query for all other data
$Asset_result =  query("SELECT `ASSET_NUMBER` from checkin ORDER BY `ASSET_NUMBER` ASC", $WHD_DB);
$Date_result = query("SELECT `DATE` from checkin ORDER BY `ASSET_NUMBER` ASC", $WHD_DB);
$Time_result = query("SELECT `TIME` from checkin ORDER BY `ASSET_NUMBER` ASC", $WHD_DB);
$Student_6D_Result = query("SELECT `STUDENT_6D` from checkin ORDER BY `ASSET_NUMBER` ASC", $WHD_DB);
$Student_first_name_result = query("SELECT `STUDENT_FRIST_NAME` from checkin ORDER BY `ASSET_NUMBER` ASC", $WHD_DB);
$Student_last_name_result = query("SELECT `STUDENT_LAST_NAME` from checkin ORDER BY `ASSET_NUMBER` ASC", $WHD_DB);
$Status_result = query("SELECT `STATUS` from checkin ORDER BY `ASSET_NUMBER` ASC", $WHD_DB);
$Charger_result = query("SELECT `Charger` from checkin ORDER BY `ASSET_NUMBER` ASC", $WHD_DB);
$Assessment_result = query("SELECT `ASSESSMENT` from checkin ORDER BY `ASSET_NUMBER` ASC", $WHD_DB);
$Ticket_number_result = query("SELECT `TICKET_NUMBER` from checkin ORDER BY `ASSET_NUMBER` ASC", $WHD_DB);

//Put into an array... I don't know what I am doing here...
$Asset = mysql_fetch_row($Asset_result);
$Date = mysql_fetch_row($Date_result);
$Time = mysql_fetch_row($Time_result);
$Student_6D = mysql_fetch_row($Student_6D_Result);
$Student_first_name = mysql_fetch_row($Student_first_name_result);
$Student_last_name = mysql_fetch_row($Student_last_name_result);
$Status = mysql_fetch_row($Status_result);
$Charger = mysql_fetch_row($Charger_result);
$Assessment = mysql_fetch_row($Assessment_result);
$Ticket_number = mysql_fetch_row($Ticket_number_result);

?>
<table border="1">
<tr>
<td>Asset #</td>
<td>Check-in Date</td>
<td>Check-in Time</td>
<td>Student's ID #</td>
<td>Student's First Name</td>
<td>Student's Last Name</td>
<td>Status</td>
<td>Charger checked in?</td>
<td>System assessment</td>
<td>Ticket number</td>
</tr>

<?php
$i = 0;
// Because the array starts at 0, this will work
while ($i < $Number) {
    echo "\n<tr>";
    echo "\n\t<td>" . $Asset[$i] . "</td>" ;  
    echo "\n\t<td>" . $Time[$i] . "</td>";  
    echo "\n\t<td>" . $Date[$i] . "</td>";  
    echo "\n\t<td>" . $Student_6D[$i] . "</td>";  
    echo "\n\t<td>" . $Student_first_name[$i] . "</td>";  
    echo "\n\t<td>" . $Student_last_name[$i] . "</td>";  
    switch($Status[$i]) {
        default:
            echo "\n\t<td>How the hell did this happen?</td>";  
        break;
        case "0":
            echo "\n\t<td>No status set</td>";  
        break;
        case "1":
            echo "\n\t<td>Acceptable</td>";  
        break;
        case "2":
            echo "\n\t<td>Dirty/Messy</td>";  
        break;
        case "3":
             echo "\n\t<td>Failure/Damage</td>";  
        break;
    }
    switch($Charger[$i]) {
        default:
            echo "\n\t<td>How the hell did this happen?</td>";  
        break;
        case "0":
            echo "\n\t<td>Charger status not set</td>";  
        break;
        case "1":
            echo "\n\t<td>Yes</td>";
        break;
        case "2":
            echo "\n\t<td>No</td>";
        break;
    }
    echo "\n\t<td>" . $Assessment[$i] . "</td>";  

    if ($Ticket_number[$i] == 0 ) {
        echo "\n\t<td>No #</td>";         
    } else {
        // Because we don't know the ticket number, lets assume if it is not zero, it has a valid number
        echo "\n\t<td><a href=http://mattmckay.org:8081/helpdesk/WebObjects/Helpdesk.woa/wa/ticket?ticketId=" . $Ticket_number[$i] . ">" . $Ticket_number[$i] . "</td>";
    }    
    echo "\n</tr>";
    $i++; 
}
echo "</table>\n";

PHP: 
function query($query,$database) {
    $result = mysql_query ($query, $database) or die("Query failed : " . mysql_error());
    return $result;
    }
 
try this. note that the keys of the array for each row are case sensitive to whatever the actual case of the columns in the table (i think). that is, if the columns are actually lowercase when you created the table, you will need to correct the query as well as each reference to $row['SOME_KEY']
PHP: 
<?php
$conn = mysql_connect('host','user','pass');
if (!$conn) die('Failed to connect to DB');
if (!mysql_select_db($WHD_DB)) die('Failed to select DB');

$result =  mysql_query("SELECT * from checkin ORDER BY `ASSET_NUMBER` ASC");
?>
<table border="1">
<tr>
<td>Asset #</td>
<td>Check-in Date</td>
<td>Check-in Time</td>
<td>Student's ID #</td>
<td>Student's First Name</td>
<td>Student's Last Name</td>
<td>Status</td>
<td>Charger checked in?</td>
<td>System assessment</td>
<td>Ticket number</td>
</tr>
<?php
while ($row = mysql_fetch_assoc($result)) {
    echo "\n<tr>";
    echo "\n\t<td>" . $row['ASSET_NUMBER'] . "</td>" ;  
    echo "\n\t<td>" . $row['TIME'] . "</td>";  
    echo "\n\t<td>" . $row['DATE'] . "</td>";  
    echo "\n\t<td>" . $row['STUDENT_6D'] . "</td>";  
    echo "\n\t<td>" . $row['STUDENT_FIRST_NAME'] . "</td>";  
    echo "\n\t<td>" . $row['STUDENT_LAST_NAME'] . "</td>";  
    switch($row['STATUS']) {
        default:
            echo "\n\t<td>How the hell did this happen?</td>";  
        break;
        case "0":
            echo "\n\t<td>No status set</td>";  
        break;
        case "1":
            echo "\n\t<td>Acceptable</td>";  
        break;
        case "2":
            echo "\n\t<td>Dirty/Messy</td>";  
        break;
        case "3":
             echo "\n\t<td>Failure/Damage</td>";  
        break;
    }
    switch($row['Charger']) {
        default:
            echo "\n\t<td>How the hell did this happen?</td>";  
        break;
        case "0":
            echo "\n\t<td>Charger status not set</td>";  
        break;
        case "1":
            echo "\n\t<td>Yes</td>";
        break;
        case "2":
            echo "\n\t<td>No</td>";
        break;
    }
    echo "\n\t<td>" . $row['ASSESSMENT'] . "</td>";  

    if ($row['TICKET_NUMBER'] == 0 ) {
        echo "\n\t<td>No #</td>";         
    } else {
        // Because we don't know the ticket number, lets assume if it is not zero, it has a valid number
        echo "\n\t<td><a href=http://mattmckay.org:8081/helpdesk/WebObjects/Helpdesk.woa/wa/ticket?ticketId=" . $row['TICKET_NUMBER'] . ">" . $row['TICKET_NUMBER'] . "</td>";
    }    
    echo "\n</tr>";
}
echo "</table>\n";
?>
of course, if your query() function returns a valid mysql result, you can replace the mysql_connect stuff and the mysql_query, presumably you've already connected to the db.
 
just realized you actually posted your query function.
you should note that using $database as an argument is misleading. it's mysql_query($query[, $db_cnx]); where $db_cnx is the link to the connection, not the database name, it is unclear which you intended to use.
 
With a little tweaking (case on the tables), it worked great!
I also applied your suggestion to my query function.

Thanks tim_m, very informative and helpful as usual.
 
glad to be of service. i remember when i was trying to figure this stuff out not too long ago
 
You must log in or register to reply here.
Back
Top