• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 327
  • Last Modified:

PHP and MySQL troubleshooting

I am trying to get few field from index.html page and trying to insert the data into my mysql database.

When I tried to insert data it is not working. I don't see DB get updated. Please advise .




<html>
<body>

<form action="index.php" method="post">
First Name: <input type="text" name="fname"><br>
Last Name: <input type="text" name="lname"><br>
email: <input type="text" name="email"><br>
<input type="submit">
</form>

</body>
</html>



=====================================================================

<html>
<body>
<div style="width: 100%; font-size: 40px; font-weight: bold; text-align: center;">
<?php
$fname = $_POST["fname"];
$lname = $_POST["lname"];
$email = $_POST["email"];

echo $fname;


ini_set('display_errors', 'On');
error_reporting(E_ALL);

print Date("Y/m/d");

$connect=mysqli_connect("localhost","user","password","student");

if(mysqli_connect_errno()){
        echo "Failed to connect to MYSQL:".mysqli_connect_error();
}

$sql = 'insert into student_info (fname,lname,pnum) values($fname,$lname,$email)';

#$result=mysqli_query($connect,"SELECT* FROM student_info");
#$result=mysqli_query($connect,"insert into student_info (fname,lname,pnum) values($fname,$lname,$email)");

#while($row = mysqli_fetch_array($result)){
        #echo $row['fname']." ".$row['lname'];
        #echo "<br>";
#}
?>
</div>
</body>
</html>
0
linuxperson
Asked:
linuxperson
  • 6
  • 5
  • 2
1 Solution
 
Dave BaldwinFixer of ProblemsCommented:
mysqli_fetch_array($result) will return the results of a previous SELECT statement.  You have that commented out.  Actually you have your INSERT commented out too.  This is what it should look like.
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
 "http://www.w3.org/TR/html4/loose.dtd">

<html>
<head>
<title>Untitled</title>
</head>
<body>
<div style="width: 100%; font-size: 40px; font-weight: bold; text-align: center;">
<?php
$fname = $_POST["fname"];
$lname = $_POST["lname"];
$email = $_POST["email"];

echo $fname;

ini_set('display_errors', 'On');
error_reporting(E_ALL);

print Date("Y/m/d");

$connect = mysqli_connect("localhost","user","password","student");

if(mysqli_connect_errno()) {
        echo "Failed to connect to MYSQL:".mysqli_connect_error();
}

$sql = 'INSERT INTO student_info (fname,lname,pnum) values('$fname','$lname','$email')';

$result = mysqli_query($connect,$sql);

$result = mysqli_query($connect,"SELECT * FROM student_info");

while($row = mysqli_fetch_array($result)) {
        echo $row['fname']." ".$row['lname'];
        echo "<br>";
}
?>
</div>
</body>
</html>

Open in new window

0
 
Ray PaseurCommented:
Dave is right about the commented-out part.  Comments have no affect in the execution of a script.
http://php.net/manual/en/language.basic-syntax.comments.php

I would only add that most PHP instructions have some kind of return value.  Whenever you use a PHP function or instruction, you would be well-advised to read the online manual before you use the function.  The online manual will tell you what your script can expect to get back from the function call.  Once you know that, you will know whether it is safe to ignore the return values.  In most cases, it's not safe to ignore the return values because they contain indicators of success or failure.

In the case of MySQL function calls, it's particularly bad practice to ignore the return values.  MySQL is not a black box -- it can and will fail for reasons that are beyond your program's control, and it's important to recognize, visualize and account for these failures in your programming.  Otherwise when this happens, you'll have a failure and no way to understand what wen wrong or what data damage occurred.  Here's an example showing the return values from mysqli_query().
http://php.net/manual/en/mysqli.query.php

Return Values

Returns FALSE on failure. For successful SELECT, SHOW, DESCRIBE or EXPLAIN queries mysqli_query() will return a mysqli_result object. For other successful queries mysqli_query() will return TRUE.
Here's an example showing how to visualize the error condition if mysqli_query() returns FALSE.
http://php.net/manual/en/mysqli.error.php

Return Values

A string that describes the error. An empty string if no error occurred.
0
 
linuxpersonAuthor Commented:
mysql> describe student_info;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| fname | varchar(20) | YES  |     | NULL    |       |
| lname | varchar(20) | YES  |     | NULL    |       |
| email | varchar(60) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
linuxpersonAuthor Commented:
mysql> select * from student_info;
Empty set (0.00 sec)
0
 
linuxpersonAuthor Commented:
Here is my new index.php file.

I am getting information from index.html via POST method and trying to insert into the table.


<?php
$fname = $_POST["fname"];
$lname = $_POST["lname"];
$email = $_POST["email"];

echo $fname;

ini_set('display_errors', 'On');
error_reporting(E_ALL);

print Date("Y/m/d");

$connect = mysqli_connect("localhost","user","password","student");

if(mysqli_connect_errno()) {
        echo "Failed to connect to MYSQL:".mysqli_connect_error();
}

$sql = 'INSERT INTO student_info (fname,lname,email) values('$fname','$lname','$email')';

$result = mysqli_query($connect,$sql);

$result = mysqli_query($connect,"SELECT * FROM student_info");

while($row = mysqli_fetch_array($result)) {
        echo $row['fname']." ".$row['lname'];
        echo "<br>";
}
?>
0
 
linuxpersonAuthor Commented:
Here is my html code which calls index.php

<html>
<body>

<form action="index.php" method="post">
First Name: <input type="text" name="fname"><br>
Last Name: <input type="text" name="lname"><br>
email: <input type="text" name="email"><br>
<input type="submit">
</form>

</body>
</html>
0
 
Ray PaseurCommented:
I can't test this because I don't have your database, but here is a starting point that might be helpful.  Some annotation...

Line 8: Set the error reporting to the highest possible level always, right at the top of the script.  That way you won't have to guess when something goes wrong - PHP will tell you.

Line 15: Like error reporting, database connections are an important part of the script.  Make these connections unconditionally right at the logical top of your application.  You wouldn't believe how many hard-to-find bugs arise from putting the database connection inside a conditional code block!

Line 26: This is the start of the action script (the script that is run after the form is submitted.  Wise practices include writing your code with a standard indentation for each inflection in the flow of logic control.  I choose 4 blanks.  Code that is written this way is easy to read because you can get a visual indication of the if/else logic.

Line 32: Never use an external data value without filtering and/or sanitizing it.  All external data is tainted.  This is a security issue that cannot be overlooked.  In this case, we perform only the minimal sanitization - we escape the data so it can be used in a query string.  In a real application, we would have a great deal more code here!

Line 37: Always construct the query in a separate variable.  That way, if anything goes wrong, your program can print out the variable and you can see what the script did that caused the failure.

Line 43: When the script runs a query, you always want to test to see if the query worked.  The example shows how to run the query and check for success or failure.

Line 55: Another query - same pattern, using a separate variable and error-checking.

Line  69: Retrieve the results, if any, from the SELECT query.  Note the use of fetch_object().  The syntax associated with fetch_object() results is easier to get right (no fiddly quotes) and to use in PHP quoted strings.  It's also more efficient than fetch_array() since it only returns one copy of the results data.

Line 81: At the end of the action script, we are also at the end of a control structure, so we stop indenting the code.

Line 87: We use HEREDOC notation to create the HTML document.  HEREDOC is great for templating, and plays especially well with results sets gotten with fetch_object() methods.

<?php // demo/temp_linuxperson.php

/**
 * See http://www.experts-exchange.com/Programming/Languages/Scripting/PHP/Q_28617271.html#a40610948
 */


/**
 * Set the error reporting so that it will be possible to determine if there are errors
 */
ini_set('display_errors', TRUE);
error_reporting(E_ALL);


/**
 * The data base connection will always be needed.  Make it a part of script initialization.
 * Make a hard-fail if the connection cannot be made, since the script cannot run without it.
 */
$connect = mysqli_connect("localhost","user","password","student");
if(mysqli_connect_errno())
{
    trigger_error("Failed to connect to MYSQL: " . mysqli_connect_error(), E_USER_ERROR);
}


/**
 * If there has been a POST-method request, $_POST will contain data.
 * This data must be sanitized before it can be used in the script.
 */
if (!empty($_POST))
{
    $fname = !empty($_POST['fname']) ? mysqli_real_escape_string($connect, $_POST['fname']) : NULL;
    $lname = !empty($_POST['lname']) ? mysqli_real_escape_string($connect, $_POST['lname']) : NULL;
    $email = !empty($_POST['email']) ? mysqli_real_escape_string($connect, $_POST['email']) : NULL;


    /**
     * Construct a query to INSERT the escaped data into the 'student_info' table
     */
    $sql = "INSERT INTO student_info (fname,lname,email) values('$fname','$lname','$email')";


    /**
     * Run the query and test for success or failure.
     */
    $res = mysqli_query($connect,$sql);
    if (!$res)
    {
        $msg = "Query Failure: $sql <br>";
        $err = mysqli_error($connect);
        trigger_error($msg . $err, E_USER_ERROR);
    }


    /**
     * If the script gets this far, the INSERT query did not fail
     * Construct a SELECT query to retrieve data from the 'student_info' table
     */
    $sql = "SELECT * FROM student_info";
    $res = mysqli_query($connect, $sql);
    if (!$res)
    {
        $msg = "Query Failure: $sql <br>";
        $err = mysqli_error($connect);
        trigger_error($msg . $err, E_USER_ERROR);
    }


    /**
     * If the script gets this far, the SELECT query did not fail
     * There might be a results set from the 'student_info' table
     */
    while($row = mysqli_fetch_object($res))
    {
        echo PHP_EOL;
        echo "Name: $row->fname $row->lname, Email: $row->email";
        echo "<br>";
    }


    /**
     * This is the end of the action script.  Close the control structure.
     */
}


/**
 * Put up a form to receive client input.
 */
$form = <<<EOD
<form method="post">
<br><input name="fname" placeholder="First Name" />
<br><input name="lname" placeholder="Last Name" />
<br><input name="email" placeholder="E-Mail Address" />
<br><input type="submit" />
</form>
EOD;

echo $form;

Open in new window


If you're new to PHP and want some dependable learning resources, this article can help you find those.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_11769-And-by-the-way-I-am-new-to-PHP.html

If you want to know some (but not all) of the things to avoid in PHP, this article can help you avoid the common anti-practices that plague many new PHP programmers.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_12293-AntiPHPatterns-and-AntiPHPractices.html

In some of the earlier code examples, I saw cases where the quotation marks were misused.  PHP is very particular about quotation marks, and failure to get them exactly right can leave latent bugs in your scripts, potentially leading to a run-time failure.  Here's what you need to know about quotes.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_12241-Quotation-Marks-in-PHP.html

PHP has its own security pages - mandatory reading for developers who use data from any external source.
http://php.net/manual/en/security.php

Hope those resources are helpful.  It's a lot to learn and PHP is a tough language to get right because of all the different ways you can do things.  But if you're patient and try to find the best practices, you can succeed with PHP.  Best of luck, ~Ray
0
 
linuxpersonAuthor Commented:
thanks

**works**

mysql_connect("localhost","user","password");
mysql_select_db("student");
mysql_query("INSERT INTO student_info (fname,lname,email) values('$fname','$lname','$email')");

**not working**
mysqli_connect("localhost","user","password","student");
mysqli_query("INSERT INTO student_info (fname,lname,email) values('$fname','$lname','$email')");


what is the difference using mysql_connect and mysqli_connect ?
0
 
Ray PaseurCommented:
MySQL is the obsolete extension that is being removed from PHP.  Do not use it.  Further explanation here:
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html

MySQLi will still be available.  

"**not working**" doesn't really help us to help you.  We need to see the error messages.  In the post above, I tried to show you how to get the error messages.  Once you've got those, please post back and we can try the next steps.
0
 
linuxpersonAuthor Commented:
Thanks Ray.

I will post. I really thanks for all your help so far.
0
 
Ray PaseurCommented:
Good!  Once we see the error messages we will know where to go next.
0
 
Dave BaldwinFixer of ProblemsCommented:
mysqli_query(...) needs the connection info.  See Procedural style on this page:
http://php.net/manual/en/mysqli.query.php
0
 
Ray PaseurCommented:
@Dave: Good catch.  I'm so used to the OOP version of MySQLi that I never think about providing the connection argument to the procedural version!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 6
  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now