6 {{ upvoteCount | shortNum }}
6 {{ upvoteCount | shortNum }}

PHP Error When retriewing data from MySQL database

by JoshuaReen
5 replies
I am learning PHP these days and have designed a test website. I'm now going to create a PHP script to display all database from MySQL database. I have created an HTML forum, with <select> tag in order to select one of three columns "First Name", "Last Name" and "Age". When I select all the three column in the from, and click the retrieve button, the code works well and displays all the database available in MySQL. But the problem occurs only, when I leave one or more than one select options. I have attached the screenshots which will clearly define what I want to say. Please provide me the solution for this.





And below is the code for HTML form and PHP and MySQL.
Code:
<?php
include "header.php";
if (isset($_POST['submit']))
{
// connect database
$con = mysql_connect("mysql5.000webhost.com", "xxxxx", "xxxxx");
// select database and create table
mysql_select_db("a8995753_db1", $con);
if(!empty($_POST['col1']))  // tutorial for this is here: http://www.webdesignerforum.co.uk/topic/36142-help-with-html-select-drop-down-and-phpmysql/
  {
  $col1=$_POST['col1'];
  }
else
  {
  $col1=NULL;
  }

if(!empty($_POST['col2']))
  {
  $col2=$_POST['col2'];
  }
else
  {
  $col2=NULL;
  }

if(!empty($_POST['col3']))
  {
  $col3=$_POST['col3'];
  }
else
  {
  $col3=NULL;
  }

$result = mysql_query("SELECT $col1, $col2, $col3 FROM Persons");
?>
<table border='1' border-color='#000000'>
<tr>
<?php
if (isset($col1))
{
echo "<th>First Name</th>";
}
if (isset($col2))
{
echo "<th>Last Name</th>";
}
if
(isset($col3))
{
echo "<th>Age</th>";
}
?>
</tr>
<?php
while($row = mysql_fetch_array($result))
  {
?>
  <tr><td> <?php  echo $row['FirstName'] ?> </td> <td> <?php echo $row['LastName'] ?> </td> <td> <?php echo $row['Age'] ?> </td></tr>
  <?php
  echo "<br />";
  }
mysql_close($con);

}
else
{
?> 
<form method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>">
<!-- radio buttons names should be the same as column names in the database. -->
<select name = "col1">
<option></option>
<option>FirstName</option>
<option>LastName</option>
<option>Age</option>
</select>

<select name = "col2">
<option></option>
<option>FirstName</option>
<option>LastName</option>
<option>Age</option>
</select>

<select name = "col3">
<option></option>
<option>FirstName</option>
<option>LastName</option>
<option>Age</option>
<input type="submit" name="submit" value="Retrieve/Update Data"></input>
</select></form>
</body>
</html>
<?php
}
?>
#data #database #error #mysql #php #retriewing
  • Profile picture of the author lightswitch
    Congratulations on learning this great language called PHP :-)
    I'd look for help on more technical forums, I bet that there must be thousands of "only PHP programming" related boards out there.
    This is more related to the Internet Marketing business, so the number of developers is not so big...
    Good luck
    {{ DiscussionBoard.errors[7098318].message }}
  • Profile picture of the author KirkMcD
    There are many ways to fix it. The easiest based on the rest of your code is to always select the three columns, but only display the ones you want.
    {{ DiscussionBoard.errors[7100550].message }}
  • Profile picture of the author chretit
    Hey, JoshuaReen

    It looks like you've put considerable effort in posting this question:

    A detailed description of the problem you're trying to solve, the entire program, and! the carefully annotated infographic.

    I'm surprised this post is not in the WSO section of the forum.

    With all that effort you've put in, I think, it's only fair to try to respond.

    KirkMcD already pointed you in the right direction. The way I'm showing here might involve more coding in the end, but there you go.

    I'm not going to write it all out for you because I don't want to deny you the satisfaction of solving it yourself, ...right?

    I hope you understand why you get the mysql error? Empty values in select boxes confuse mysql_query statement parser. And those redundant commas result in a syntax error.

    So what you could do is to build the select query into a string bit by bit while you're checking the input values.

    Define a new variable before the if statement: $cols

    And instead of doing this:
    $col1=$_POST['col1'];
    do this:
    $cols .= $_POST['col1'] . ",";

    And so forth.

    And later on, instead of:
    $result = mysql_query("SELECT $col1, $col2, $col3 FROM Persons");


    do something like this:
    $cols = $cols ? substr( $cols,0, -1 ) : "";
    if ($cols) {
    $result = mysql_query( "SELECT $cols FROM Persons" );
    }

    It removes the extra comma at the end and queries the db if the select string is not empty.

    You're not yet done. Now you'll have to display the table according to how many and which columns the user selected.

    Good luck
    {{ DiscussionBoard.errors[7103854].message }}
  • Profile picture of the author SmartWeb
    Hi,

    If you see the PHP error, it says line number 57, which is
    while($row = mysql_fetch_array($result))

    and this is the line where you are using "$result" , so you must be sure that the $result is proper data. for this you need to check your line 36, where you are doing
    $result = mysql_query("SELECT $col1, $col2, $col3 FROM Persons");

    , try echoing like this,
    echo "SELECT ".$col1.", ".$col2.", ".$col3." FROM Persons";
    this way you can see if $col1, $col2 & $col3 has any data ... if not you can debug why no data.
    Signature
    Smart Web Solutions
    {{ DiscussionBoard.errors[7126185].message }}

Trending Topics