Solved

PHP and MySQL troubleshooting

Posted on 2015-02-14
13
257 Views
Last Modified: 2015-02-27
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
Comment
Question by:linuxperson
  • 6
  • 5
  • 2
13 Comments
 
LVL 82

Expert Comment

by:Dave Baldwin
Comment Utility
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
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
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
 

Author Comment

by:linuxperson
Comment Utility
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
 

Author Comment

by:linuxperson
Comment Utility
mysql> select * from student_info;
Empty set (0.00 sec)
0
 

Author Comment

by:linuxperson
Comment Utility
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
 

Author Comment

by:linuxperson
Comment Utility
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 108

Accepted Solution

by:
Ray Paseur earned 500 total points
Comment Utility
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
 

Author Comment

by:linuxperson
Comment Utility
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
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
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
 

Author Comment

by:linuxperson
Comment Utility
Thanks Ray.

I will post. I really thanks for all your help so far.
0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
Good!  Once we see the error messages we will know where to go next.
0
 
LVL 82

Expert Comment

by:Dave Baldwin
Comment Utility
mysqli_query(...) needs the connection info.  See Procedural style on this page:
http://php.net/manual/en/mysqli.query.php
0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
@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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

It is possible to boost certain documents at query time in Solr. Query time boosting can be a powerful resource for finding the most relevant and "best" content. Of course the more information you index, the more fields you will be able to use for y…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now