Table not updating on webserver mysql 5.5

[pasquith]

Hi, can anyone shed any light on what is happening please?

 

I am running a php 7 server hosted by 1&1 (first mistake lol) they also host the MySQL 5.5 server (Debian OS I think) I am trying to update.

 

I have created a php form tested it on my laptop hosted server, originally a MySQL 5.7 server, and it worked. Uploaded to 1&1 and it failed.

 

So I cut down the code as much as possible, making it as basic as possible, just to try and get the code to update the database. I know the code is currently wide open to security issues and abuse, however I will secure it again once I can get the script to work, I am just trying to keep it as simple as possible. Tried it on the laptop and it worked, on the website, and it failed.

 

I have checked everywhere that I can find, books and online. I just can't see the error... that means it has to be simple, and I am being an idiot right? ;-)

 

Trying the tech support on 1&1 was laughable, if it wasn't so tragic.  

 

I dropped the MySQL server on my laptop down to 5.5 found the script didn't work, it needed to be told to use the dbase explicitly, changed that and it worked... on the laptop... on the server no joy. 

 

it just seemed to be something about the server but I can't see what.

 

This is the code

 

 

<?php //wmsqltest.php
require_once 'login.php';
$conn=new mysqli($hn, $un, $pw, $db);
if ($conn->connect_error) die($conn->connect_error);
 
if (isset($_POST['email']) &&
isset($_POST['fname']) &&
isset($_POST['lname']) &&
isset($_POST['address']) &&
isset($_POST['pcode']) &&
isset($_POST['children']) &&
isset($_POST['cnames']) &&
isset($_POST['c_age']) &&
isset($_POST['doctor']) &&
isset($_POST['where_refer'])) 
{    //questions about you
$email =get_post($conn, 'email');
$fname =get_post($conn, 'fname');
$lname =get_post($conn, 'lname');
$address =get_post($conn, 'address');
$pcode =get_post($conn, 'pcode'); 
$children =get_post($conn, 'children');
$cnames =get_post($conn, 'cnames');
$c_age =get_post($conn, 'c_age');
$doctor =get_post($conn, 'doctor');
$where_refer =get_post($conn, 'where_refer');
 
//next series about how we can help you
$query = "USE hypnotherapy; INSERT INTO weight_management VALUES " . 
"('$email', '$fname', '$lname', '$address', '$pcode', '$children', '$cnames', '$c_age', '$doctor', '$where_refer')";
$result =$conn->query($query);
if (!$result) echo "INSERT failed: $query<br>" . 
$conn->error . "<br><br>";
}
echo <<<_END
<form action="wmsqltest.php" method="post"><pre> 
email <input type="text" name="email">
first name <input type="text" name="fname">
last name <input type="text" name="lname">
Home Address <input type="text" name="address">
Home Postcode <input type="text" name="pcode">
Do you have any children? <input type="text" name="children">
If so what are their names? <input type="text" name="cnames">
and their ages. <input type="text" name="c_age">
Who is your Doctor? <input type="text" name="doctor">
Where did you hear about us? <input type="text" name="where_refer">
<input type="submit" value="ADD RECORD">
</pre></form>
_END;
 
function get_post($conn, $var)
{
return $conn->real_escape_string($_POST[$var]);
}
?>

[Psycho]

There's problems aside from your query not running.

 

1. You should be using prepared statements. You can do it with mysqli, but everything is much easier with PDO. You shouldn't be using real_escape_string() to make the data safe.

 

2. Not sure what the purpose of all the isset() checks since you don't seem to have any validation on whether they actually contain data or not. You could have just a single check using the REQUEST_METHOD to ensure the form is posted - then inside the if() condision do any validation of the data submitted.

 

3. As to your query, I have always listed the fields after the INSERT statement before VALUES. Example

INSERT INTO table_name
    (fielda, fieldb, fieldc) -- << I don't see this in your query
VALUES
    ('value1a', 'value1b', 'value1c'),
    ('value2a', 'value2b', 'value2c'),
    ('value3a', 'value3b', 'value3c')

I think that is a poor practice, but it will work if you have the same number of fields in your VALUES that you have in the table and there are no data type issues. Perhaps the table on your laptop is different than the one on your host. i.e. field definitions, field order, etc. Try listing the fields as part of the insert statement.

[gizmola]

I edited your post to add code tags, and in the process I inadvertently seemed to have lost your error message. Mea Culpa in advance...

 

With that said, we are on shaky ground when you make statements that are completely false.  There is no way this code worked:

 

$query = "USE hypnotherapy; INSERT INTO weight_management VALUES " . 
"('$email', '$fname', '$lname', '$address', '$pcode', '$children', '$cnames', '$c_age', '$doctor', '$where_refer')";

Mysql does not allow you to batch sql statements.

 

 

The "use" of databases is accomplished when you connect. You will use the database you specify in your connect statement. There is a way to change the currently selected database, but there is no reason for you to need that in your script. You simply can't add it to the front of your sql statement and pass it. That is not allowed with the mysql api.

 

As Psycho pointed out, the insert statement is also missing the column list. Can you get away with no column list? Yes, but only if your number of values exactly matches the number of columns in the table.

 

Few people will take advantage of that fact, as it's poorly documented at best. If indeed that is an exact match for your column list, then your weight_management table apparently has no obvious primary key. Every table should have a primary key, ideally an UNSIGNED INTEGER AUTO_INCREMENT.

 

If the column list doesn't match, then the insert statement is going to fail with an error like: "ERROR 1136 (21S01): Column count doesn't match value count at row 1"

 

Again unfortunately I lost your error text. If you want to post a follow up, I'll restore it to your original question.

 

What I'd suggest in the meantime is fix your sql statement at the very least. Something like:

 

$query = "INSERT INTO weight_management (col1name, col2name, col3name...etc) VALUES ('$email', '$fname', '$lname', '$address', '$pcode', '$children', '$cnames', '$c_age', '$doctor', '$where_refer')";

[pasquith]

Hi again. Thank you both for your for your help.

 

You are right Psycho I know I should be using PDO, but I was trying to boil the code down as much as possible to strip out anything that could cause the error, (because I will probably get something wrong there too, lol. Also the table I created from an .sql source in both systems to ensure consistency, and not used a primary key field because I was just trying to get the connection to work just once. But I will add the field names as you both suggest as soon as I get home from work, and post the results

 

You are also right gizmola, I had added the Use database after it worked on my laptop, in desperation in case that was what it needed.

 

In fact so far the only things working on the 1&1 server is the connection script from php reports a successful connection! oh and any sql I enter using the phpmyadmin software.

 

Thanks again I will update later.

[Jacques1]

Reducing a problem is great, but when you also strip out all the parts which are required to prevent errors, you've effectively created a second problem.

 

So I suggest you start over, this time with a correctly(!) written minimal example.

• If there's any chance you can switch to PDO, do it now and forget about mysqli altogether. mysqli is a hard-core low-level interface for very experienced programmers who love to study the manual and make sure every single line of their code is perfect. Let's be honest: You aren't that kind of guy. PDO is a much more programmer-friendly option which you can learn fairly quickly.
• Forget about escaping and use prepared statements instead. Contrary to popular belief, this isn't "just" a matter of security. The goal of prepared statements is to prevent the input from fucking up the query, regardless of whether that input comes from an evil attacker or just happens to contain characters which cause syntax problems (like "O'Reilly" in a single-quoted string).
• Set up proper error handling and reporting. Both PDO and mysqli support exceptions which are automatically triggered in case of an error, so you can get rid of the cumbersome (and incorrect) die(error_message) stuff.
• Write proper SQL queries. No lazy shortcuts.

Chances are this will already fix your problem. If it doesn't, post the new code together with the full exception message.

[pasquith]

Hi again and thank you all for your support.

 

As you already guessed it's working, writing proper SQL is the key.

 

I am just working through Robert Nixon's learning php, MySQL & JavaScript, from O'reilly, and the text about INSERT shows the fields then the VALUES. But then the examples of integrating with php uses the script as above. I think it is a really good book, but just goes to show two servers can be different and this needs lots of practice and experience.

 

Ok now I know it works and that errors have to be my code, onward to PDO.

 

Thank you all again.

[gizmola]

I'm glad you are taking the advice to learn PDO. Read through the site Jacques posted. It's a free book quality tutorial and manual on PDO that walks you through everything you need to know.