show everything in table

[daniel0816]

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);
  }

[litebearer]

you need to loop thru the table

read here - http://www.tutorialspoint.com/php/mysql_select_php.htm

[denno020]

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

[.josh]

mysql_query returns a resultsource upon success; an object containing your data. In order to get the data out of $sql, you will need to use a function like mysql_fetch_assoc. Look at example #2 of the mysql_query for details.

[litebearer]

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";
}
?>

 

[daniel0816]

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";

[.josh]

each line needs to end in a semicolon. The line before the one you pointed out has a dot, adn the one you point at has nothing.

[daniel0816]

nvm got it sorted thanks for the help

[Psycho]

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>