Jump to content

show everything in table


daniel0816

Recommended Posts

I am trying to show everything that's in the CustomerTable, it just displays the echo I have set i.e. successful. There is data in the table. Don't know why this isn't working any help would be greatly appreciated thanks.

 

//Show tables in DB
$sql = "SELECT * FROM CustomerTable";
 // Execute query
 if (mysql_query($sql, $connect))
  {
  echo "successful!";
  }
else
  {
  echo "Error: " . mysql_error($connect);
  }

Link to comment
Share on other sites

You need to assign mysql_query() to a variable, and then you can use that as your if condition (or just assign it in the if condition). Then instead of echo'ing succesful, you var_dump or print_r the variable:

 

 

if($result = mysql_query($sql, $connect)){
var_dump($result);
}

 

That will be the quickest way to get output. It's not the prettiest, nor is it using functions that you should be using, but it will work.

 

Denno

Link to comment
Share on other sites

The following script will display a tables structure as well as its contents...

<?php
/*
    this small script can be used to
        (a) get and display a table's structure (field names and field type)
        (b)    get and display the field contents in an html table
*/

/* set the database connection variables */
$database="";
$location = "";  
$username = "";  
$password = "";  
$db_table = "";

/*
set the function switches
    0 means use
    1 means do NOT use
*/

$display_table_structure = 0;
$display_table_contents = 0;
$csv_name = $db_table . "_" . time();
$how_many = 0; /* use this value to limit the number of records returned in the data query (0 means no limit) */
 
/* Start Defining the function(s) */


/*        This function gets the field names */
function my_db_get_table_field_names($table) {
    $sql = "SHOW COLUMNS FROM `$table`";
    $field_names = array();
    $result2 = mysql_query($sql);
    for($i=0;$i<mysql_num_rows($result2);$i++){
        $row = mysql_fetch_array($result2);
        $name = $row['Field'];
        array_push($field_names, $name);
    }
    return $field_names;
}



/* connect to the database */
mysql_connect ($location, $username, $password);
mysql_select_db($database) or die( "Unable to select database");
$result0 = mysql_query("SHOW COLUMNS FROM $db_table");

if (!$result0) {
  echo 'Could not run query: ' . mysql_error();
  exit;
}


/*    get the field names into an array and count them */
$new_array = my_db_get_table_field_names($db_table);
$fields = count($new_array);

/* get all the data and count the records */

if ($how_many == 0) {
    $result = mysql_query( "SELECT * FROM $db_table" )
    or die("SELECT Error: ".mysql_error());
    $num_rows = mysql_num_rows($result);
}else{
    $result = mysql_query( "SELECT * FROM $db_table LIMIT $how_many" )
    or die("SELECT Error: ".mysql_error());
    $num_rows = mysql_num_rows($result);
}

/* display the general information */
echo "This information is for the " . $database . " database. Table " . $db_table . "<br><br>";
echo "There are " . $fields . " columns/fields  and " . $num_rows . " records.<br><br>";

/*    display the table structure */
if ($display_table_structure == 0) {
        echo "<hr>Table structure for " . $db_table . ": <br><br>";
        echo "<table border=1> <tr><td>Field #</td><td>Field Name</td><td>Field Type</td></tr>";
    $result9 = mysql_query("SHOW FIELDS FROM $database.$db_table");
    $i = 0;
    while ($row = mysql_fetch_array($result9)){
        echo "<tr><td>" . $i . "</td><td>" . $row['Field'] . "</td><td>" . $row['Type'] . "</td></tr>";
    $i = $i +1;
    }
        echo "</table>";
}

/* display the table contents */
if ($display_table_contents == 0) {
    echo "<hr>Table data for " . $db_table . ": <br><br>";
    echo "<table width='100%' border=1>\n";
    $i = 0;
    echo "<tr>\n";
    for ($i=0;$i<$fields;$i++) {
      echo "\t<td><font face=arial size=1/>$new_array[$i]</font></td>\n";
    }
    while ($get_info = mysql_fetch_row($result)){
        echo "<tr>\n";
        foreach ($get_info as $field)
            echo "\t<td><font face=arial size=1/>$field</font></td>\n";
            echo "</tr>\n";
    }
    echo "</table>\n";
}
?>

 

Link to comment
Share on other sites

This is my code now:

I am now getting the following error:

Parse error: syntax error, unexpected 'echo' (T_ECHO)  on line 95

 

//Show tables in DB
$sql = "SELECT * FROM CustomerTable";
 // Execute query
 $retrieval = mysql_query($sql, $connect);
 if(!$retrieval){
 die('Could not get data: ' . mysql_error());
 }
 while($row = mysql_fetch_array($retrieval, MYSQL_ASSOC))
 {
 echo "FORENAME: {$row['forename']} <br>".
 echo "SURNAME: {$row['surname']}  <br>"                       <-------------------------------------- Line 95
 echo "CONTACT NUM: {$row['mobileNum']} <br>";
 }
  echo "Fetched data successfully\n";

Link to comment
Share on other sites

Also, for what it's worth, it doesn't hurt to have logic to handle situations where no data is returned. In this example may not be an issue, but when you develop more involved queries, getting feedback that there were no results, rather than showing nothing, is a lot more informative. Also, it's a good idea to stick with variable names that are widely recognized for common processes rather than coming up with your own. For one thing, it makes it much easier for others to help you if they don't have to trace back through code to determine what a variable contains. Having said that, I would use $result as the result returned from the query. And, while die() is a quick and effective way to troubleshoot, you should not use it in your final code. You should create logic to gracefully handle error conditions to provide users with a general message about the problem, but also have a way for you to get the specific details about the error.

 

Example:

 

<?php

//Create and execute query
$sql = "SELECT * FROM CustomerTable";
$result = mysql_query($sql, $connect);

$output = '';
if(!$result)
{

    $output = "There was a problem getting the data. If the problem persists, please contact the administrator.";
    //You can create a process to set a debug mode variable to obtain more descriptive error information
    //This should be able to be set for a user rather than the entire application. Once quick and dirty method is
    //to add a debug=1 onto the query string and have the code use that to set a session variable. So, you can turn
    //on debug mode for yourself in a production environment while not affecting other users
    if($DEBUG)
    {
        //Provide a more descriptive error that only YOU will see
        $output .= "Query:<br>{$sql}<br>Error:<br>" . mysql_error();
    }
}
elseif(!mysql_num_rows($result))
{
    $output = "There were no results";
}
else
{
    $output = "There were " . mysql_num_rows($result) . " results: <br>\n";
    while($row = mysql_fetch_array($retrieval, MYSQL_ASSOC))
    {
        $output .= "FORENAME: {$row['forename']} <br>\n";
        $output .= "SURNAME: {$row['surname']}  <br>\n";
        $output .= "CONTACT NUM: {$row['mobileNum']} <br><br>\n";
    }
}
?>
<html>
<head></head>
<body>
<?php echo $output; ?>
</body>
</html>
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.