Insert data in phpmyadmin

sigmahokies · April 29, 2015

Hello php freak people,

I hope you can help me solve why #30 line can not solve the problem to query to allow fetch row in php...error log show "$FirsttName" is stopped by syntax error.

Here my script:

<?php require('connection.php');
?>
<html>
<head>
<link href="dnocolor.css" rel="stylesheet" type="text/css">
</head>
<body>
<p>Welcome to Gary Taylor's DNO website</p>
<p>Please register below here if you want to go</p>
<form action="" method="GET">
<fieldset>
<table><tr><td colspan="4" align="center">Please type your name below here:</td></tr>
<tr><td>First Name:</td><td><input type="text" name="data" value="<?php if(isset($_GET['Firstname'])) echo $_GET['FirstName']; ?>"></td><td>Last Name:</td><td><input type="text" name="data" value"<?php if(isset($_GET['Firstname'])) echo $_GET['LastName']; ?>"></td></tr>
<tr><td>City:</td><td><input type="text" name="data" value="<?php if(isset($_GET['City'])) echo $_GET['City']; ?>"></td><td>State:</td><td><input type="text" name="data" value="<?php if(isset($_GET['State'])) echo $_GET['State']; ?>"></td></tr>
<tr><td colspan="4" align="center"><input type="submit" id="submit" value="You are going"></td></tr>
</table>
</fieldset>
</form>
<hr>
<?php
if($_GET){
$fon = mysqli_connect('XXXXXXX','XXXXXXXXX','XXXXXXXX','XXXXXXXX');}
if($fon === false){
die("Error, could not connect ".mysqli_connect_error());
}
$FirstName = mysqli_real_escape_string($fon, $_GET['FirstName']);
$lastName = mysqli_real_escape_string($fon, $_GET['LastName']);
$City = mysqli_real_escape_string($fon, $_GET['City']);
$state = mysqli_real_escape_string($fon, $_GET['State']);
$insert = 'INSERT INTO 'XXXXXXXXXX' ('ID', 'FirstName', 'LastName', 'City', 'State') VALUES (NULL,'$FirstName','$LastName','$City','$State')';
if(mysqli_query($fon, $insert)) {
echo '<p>adding your name in list successfully</p>';
}
else {
echo '<p>Sorry, I could not add your name to list</p>';
}
$show = 'SELECT FirstName, LastName, City, State FROM XXXXXXXXX';
$result = mysql_query($show);
while($row = mysql_fetch_row($result)) {
echo '<table><tr><td>'.$row.'</td></tr><table>';
}
?>
</body>
</html>

Barand · April 29, 2015

The query string needs to be enclosed in double quotes, not single, if you want to expand variable values in the string. Table and column names should not be in quotes

$insert = "INSERT INTO XXXXXXXXXX (ID, FirstName, LastName, City, State) VALUES (NULL,'$FirstName','$LastName','$City','$State')";

gizmola · April 29, 2015

Hello Gary,

Please in the future just use code tags around your code.

The problem is that you need to understand the different types of quotes you can use in php.

In your case your whole line should be within double quotes. Single quotes in php are string constants. Double quotes will allow variables to be interpolated into the string when the code is evaluated, which is what you want.

Currently you have all single quotes, so the parser is confused. What you want is:

$insert = "INSERT INTO 'XXXXXXXXXX' ('ID', 'FirstName', 'LastName', 'City', 'State') VALUES (NULL,'$FirstName','$LastName','$City','$State')";

Furthermore in mysql there is a different kind of quote (back tic) you use for field names. So what you want in the names section of the code is either to remove the quotes around the names of the columns or use back tics.

$insert = "INSERT INTO `XXXXXXXXXX` (`ID`, `FirstName`, LastName, City, State) VALUES (NULL, '$FirstName', '$LastName', '$City','$State')";

sigmahokies · May 4, 2015

Hi Barand, I tried that, it won't work. even I used single or double quote, still not work. Hi Gizmola, I used a quote that you show (just next left from #1 on keyboard), still not work. Do you like to see my code on this screen? of course, I won't copy all code, I will copy $insert then below...Please let me know, Thank you so much.

cyberRobot · May 4, 2015

What does line #30 look like now?

Also, have you checked to see if MySQL is throwing any errors? You can view more information on how to do that here:

http://php.net/manual/en/mysqli.error.php

sigmahokies · May 4, 2015

Hi cyberRobot, here my code:

$insert = mysqli_query 'INSERT INTO 1141650_sigma22.JoinDNO (ID,FirstName,LastName,City,State) VALUES (NULL,'$FirstName','$LastName','$City','$State')";

if(isset($insert)) {

echo '<p>adding your name in list successfully</p>';

}

else {

echo '<p>Sorry, I could not add your name to list</p>';

}

Edited May 4, 2015 by sigmahokies

cyberRobot · May 4, 2015

Since mysqli_query() is a function, the parameters / arguments need to be surrounded by parenthesis. Also, the database link needs to be provided to mysqli_query() as the first parameter since you're using the procedural style. More information can be found here:

http://php.net/manual/en/mysqli.query.php

Once you have that fixed, you'll need to fix the quotes. Your query currently opens with a single quote and closes with a double quote.

Once that's fixed, you'll want to note that the following will always evaluate to true (even if the query fails):

if(isset($insert)) {

Basically, the isset() function isn't needed. mysqli_query() will return the resultset if the query succeeds or false if the query fails.

Edited May 4, 2015 by cyberRobot

sigmahokies · May 4, 2015

Hi CyberRobot, I removed isset, but I did put two type of parameters () and {} already, but still not work...my source is in color, easy to see those words to program. If you are saying that I need to put single and double quote, that is what I did...look code below here:

$insert = mysqli_query("INSERT INTO 1141650_sigma22.JoinDNO (ID,FirstName,LastName,City,State) 
VALUES
(NULL,".$FirstName.",".$LastName.",".$City.",".$State.")");
if ($insert) {
echo '<p>adding your name in list successfully</p>';
}
else {
echo '<p>Sorry, I could not add your name to list</p>';
}

Check my website - http://sigmahokies.biz.ht/rvadno.php

I got three error response.

Barand · May 4, 2015

Both I and gizmola have given you the correct syntax. What's your problem?

FatBobsFeet · May 4, 2015

I'm not sure what your problem is if these suggestions are not helping, but it is always good to weed out other possibilities.

I noticed that on lines #38 and #39 you are using mysql functions. These are deprecated (no longer used) and should be replaced with mysqli, like you have earlier in your code.

Also, I noticed that in your opening sentence, you have the variable "$FirsttName" [<-- notice there are two of the letter t ] but in your code below it is spelled with only one. Be sure to check your actual code for misspellings; this is one common source of errors.

sigmahokies · May 4, 2015

Hi Barand, I know you gave me a correct syntax, but it does not work. I checked connection.php, access is working. I followed your advice very exactly with quotes, still not work, still getting error. Even, I research many websites about how to two to insert the data in phpmyadmin, include stackoverflow website. I just don't want to depend on Dreamweaver to do for me. I rather to writing in code in PHP.

QuickOldCar · May 5, 2015

Besides the mixed mysql and mysqli functions.....you are using a lower and upper case of the state variable

$state = mysqli_real_escape_string($fon, $_GET['State']);
$insert = 'INSERT INTO 'XXXXXXXXXX' ('ID', 'FirstName', 'LastName', 'City', 'State') VALUES (NULL,'$FirstName','$LastName','$City','$State')';

BTW, you don't need to pass ID for the insert if using autoincrement for the id's

gizmola · May 5, 2015

I took the last code you pasted, and fixed it.

$insert = mysqli_query("INSERT INTO 1141650_sigma22.JoinDNO (ID, FirstName, LastName, City, State) 
VALUES
(NULL, '$FirstName', '$LastName', '$City', '$State')";
if ($insert) {
    echo '<p>adding your name in list successfully</p>';
} else {
    echo '<p>Sorry, I could not add your name to list</p>';
}

cyberRobot · May 5, 2015

Hi CyberRobot, I removed isset, but I did put two type of parameters () and {} already, but still not work...my source is in color, easy to see those words to program. If you are saying that I need to put single and double quote, that is what I did...look code below here:
$insert = mysqli_query("INSERT INTO 1141650_sigma22.JoinDNO (ID,FirstName,LastName,City,State)

VALUES

(NULL,".$FirstName.",".$LastName.",".$City.",".$State.")");

For what it's worth, variables like $FirstName, $LastName, etc. contain string values. String values in queries need to be surrounded by quotes. Since double quotes are being used around the query so that variables work, you would use single quotes around the variables which contain strings. So you could do something like this:

<?php
$insert = mysqli_query("INSERT INTO 1141650_sigma22.JoinDNO (ID,FirstName,LastName,City,State) 
VALUES
(NULL, '".$FirstName."', '".$LastName."', '".$City."', '".$State."')");
?>

However, it's much cleaner to use gizmola's suggestion.

$insert = mysqli_query("INSERT INTO 1141650_sigma22.JoinDNO (ID, FirstName, LastName, City, State) 
VALUES
(NULL, '$FirstName', '$LastName', '$City', '$State')");

Note that I added the closing parenthesis between the end double quote and the semi-colon.

jcbones · May 5, 2015

All of this, and y'all still missed the connection as first param:

mysqli_query($connection,$query);

In this case:

$insert = mysqli_query($fon,"INSERT INTO 1141650_sigma22.JoinDNO (ID, FirstName, LastName, City, State) 
VALUES
(NULL, '$FirstName', '$LastName', '$City', '$State')");

This should work, provided you changed the $state variable declaration to $State.

Edited May 5, 2015 by jcbones

Barand · May 5, 2015

Oops!. Not usually using that method, I prefer $mysqli->query()

cyberRobot · May 6, 2015

All of this, and y'all still missed the connection as first param:
mysqli_query($connection,$query);

Well...technically I did mention it in Reply #7.

Also, the database link needs to be provided to mysqli_query() as the first parameter since you're using the procedural style.

Of course, I forgot to carry that through to my following post. :-[

sigmahokies · May 6, 2015

Thank you, everyone for advice and fixed my code, but it is still not work. I think it is company is what blocking me to do those database. I sent email technical support about my problem, they told me that they will not allow anyone using external remote control over database in phpmyadmin. Maybe that is why I said it is not working. I am using biz.ht for practice to my improve my skill in PHP, they offer me a small memory in website to use for free to use. Again, Thank you for trying to help me to understand with quotes, senstive uppercase and lowercase. I am still getting fatal error and parse error on my website in biz.ht.

sigmahokies · May 7, 2015

Hi everyone, Good news, I got myself figure it out. Missing in parameters was name of connection and dbname. Example -

require('connection.php') OR $GaryDatabase = mysqli_connect('xxxxx",'xxxxx','xxxxx','xxxxx');

mysqli_select_db($GaryDatabase, "JoinDNO") <-- that is missing

then...

$insert = mysqli_query($GaryDatabase, "INSERT INTO...")

That is how it works. i tested it many times...succeed insert data from text field.

Now, I got other problem. I just made a script in PHP to display the record (data) on display, display is working, but seem problem is I notice reload and submit the blank textfield, I can see MySQL taking the blank data in database. How can I stop/prevent MySQL taking a blank data in database? Should I disable auto increment? or what? Please advise me...

Thank you so much

Edited May 7, 2015 by sigmahokies

QuickOldCar · May 8, 2015

if($_GET){

GET always exists as an empty array

You didn't post your current code so will summarize what to do

if(isset($_GET['FirstName']) && trim($_GET['FirstName']) !=''){
    $FirstName = trim($_GET['FirstName']);
}

if(isset($_GET['LastName']) && trim($_GET['LastName']) !=''){
    $lastName = trim($_GET['LastName']);
}

if(isset($_GET['City']) && trim($_GET['City']) !=''){
    $City = trim($_GET['City']);
}

if(isset($_GET['State']) && trim($_GET['State']) !=''){
    $state = trim($_GET['State']);
}

//checking to see if all the variables exist, also can do isset() for each
if($FirstName && $lastName && $City && $state){

//then do connection,escape variables and insert

}

alternately can add your own errors

$errors = array();

if(isset($_GET['FirstName']) && trim($_GET['FirstName']) !=''){
    $FirstName = trim($_GET['FirstName']);
}else{
    $errors['firstname'] = "first name missing";
}

if(isset($_GET['LastName']) && trim($_GET['LastName']) !=''){
    $lastName = trim($_GET['LastName']);
}else{
    $errors['lastname'] = "last name missing";
}

if(isset($_GET['City']) && trim($_GET['City']) !=''){
    $City = trim($_GET['City']);
}else{
    $errors['city'] = "city missing";
}

if(isset($_GET['State']) && trim($_GET['State']) !=''){
    $state = trim($_GET['State']);
}else{
    $errors['state'] = "state missing";
}

//checking to see if all the variables exist, also can do isset() for each
if(empty($errors)){

//then do connection,escape variables and insert

}else{

foreach($errors as $error){
    echo $error."<br />";
}

}

Edited May 8, 2015 by QuickOldCar

sigmahokies · May 9, 2015

QuickOld Car, your code is working, stopping taking blank data when I submitted, thank you! but there is small problem - reload is still taking a blank data in textfield with $_GET because it will stay in textfield when it is reload page, then database will take this data in. So, I change to $_POST... Any advice?

Edited May 9, 2015 by sigmahokies

QuickOldCar · May 9, 2015

Option 1:

In the database create a unique index using a unique constraint to prevent duplicates

Since people could have the same name in the same city and also state...most likely have to do all four.

This will prevent a new registration for someone with the same values.

It's common for people with the same names in the same area.

Using more information could lessen the chances, date of birth,nickname,password comes to mind.

ALTER TABLE TableName
ADD CONSTRAINT allUnique UNIQUE(FirstName, LastName, City, State);

Option 2:

In some circumstances can set the primary as a unique and then use INSERT IGNORE, even including ON DUPLICATE KEY UPDATE

Option 3:

Redirect the user elsewhere with either header() or a meta refresh

Option 4:

Perform a query to check if all 4 values from your form returns at least one result, only if they don't exist do the insert.

Option 5:

Another method is to set a unique token in a session, then check for it, if exists then was a resubmit.

Option 6:

More extreme is to use jquery or javascript and disable the submit button after submission.

Edited May 9, 2015 by QuickOldCar

Sign In

Insert data in phpmyadmin

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information