PHP - Mysql Insert Query

How can i use single quote for values?

$qry='insert into tablename values('a','b');';

I am really just a beginner in PHP but I've got a task to create a simple hotel booking system by using PHP and MySQL. It works in several steps, described in separate .php files. All of them seem to work fine, except the last one. In this last file I want to add all the data into a MySQL database table. I would like to do this by creating a record for each date, a guest is spending in a hotel (so that when booking, another loop checks whether the room is not occupied yet for that date). I decided to use a for-loop for that, but it doesn't seem to be working (the records just don't get inserted into the table). I checked all the variables by echo-ing them and they are all right. I also tried the query without the loop and it works as well. So I guess there is some other problem. This particular .php file looks as follow:
<?php
session_start();
include('db_config.php');
    $name=($_POST['name']);
    $telephone=addslashes($_POST['telephone']);
    $email=addslashes($_POST['email']);
    $code=md5($email.time());
    $checkout_date=$_SESSION['checkout'];
    $checkin_unix=$_SESSION['checkin_unix'];
    $checkout_unix=$_SESSION['checkout_unix'];
    $roomtype=$_SESSION['roomtype'];
    $checkin_date=$_SESSION['checkin'];
    for ($stay_date=$checkin_unix; ; $stay_date=$stay_date+24*60*60) {
      if ($stay_date=$checkout_unix){
      break;
      }
      mysql_query("INSERT INTO reservations VALUES(
        '',
        '$roomtype',
        'pending',
        'date ('d/m/Y', $stay_date)',
        '$stay_date',
        '$checkout_date',
        '$name',
        '$telephone',
        '$email',
        '$code'
        )");
      
    }
?>

I would appreciate any kind of help, any idea.

I have created a registration page to access my website. After the user registrate himself should appear an alert saying that the registration was OK and a redirect to main.php page... however for some reason if I create an insert statement the alert and the redirect don't appear... If I remove the insert the alert and the redirect works... why?

This is part of the code of my 3 files:

registration.php (ajax call)

$('#submit').click(function() {
    var username2 = $('#uname2').val();
    var password2 = $('#psw2').val();
      $.ajax({
        url: 'ajax/response.php',
        type: 'GET',
        data: {username2 : username2, password2: password2},
            success: function(data) {
                if(data === 'correct') {
                    alert("Username and Password have been created!"); //don' work with the insert
                    location.replace("main.php"); //don' work with the insert
                    } else {
                         alert("Username or password are not correct... please register yourself!");
                   }
              }
       });
});

response.php (answer to ajax call)

if(isset($_GET['username2']) && isset($_GET['password2'])) {
    $username2 = $_GET['username2'];
    $password2 = $_GET['password2'];
      if (checkUser($pdo, $username2) === true) {
        echo 'duplicate';
    } else {
        insertUserPwd($pdo, $username2, $password2); //including this line the redirect and the alert doesn't work... the insert is OK
        echo 'correct';
    }
}

data_access.php (the function works but doesn't permit alert and redirect to appear)

function insertUserPwd(PDO $pdo, $usr, $pwd)
{
    $data = [
    'id' => '',
    'user' => $usr,
    'password' => $pwd
    ];
    $sql = "INSERT INTO users (id, user, password) VALUES (:id, :user, :password)";
    $stmt= $pdo->prepare($sql);
    $stmt->execute($data);
}

Can someone help me to fix the code? 

 

Can anyone tell me why this is not INSERTing? My array data is coming out just fine.. I've tried everything I can think of and cannot get anything to insert.. Ahhhh!

<?php
  $query = "SELECT RegionID, City FROM geo_cities WHERE RegionID='135'";
  $results = mysqli_query($cxn, $query);
  $row_cnt = mysqli_num_rows($results);
  echo $row_cnt . " Total Records in Query.<br /><br />";
  if (mysqli_num_rows($results)) {
      while ($row = mysqli_fetch_array($results)) {
          $insert_city_query = "INSERT INTO all_illinois SET state_id=$row[RegionID], city_name=$row[City] WHERE id = null" or mysqli_error();
          $insert = mysqli_query($cxn, $insert_city_query);
          if (!$insert) {
              echo "INSERT is NOT working!";
              exit();
          }
          
          echo $row['City'] . "<br />";
          
          echo "<pre>";
          echo print_r($row);
          echo "</pre>";
      }
      //while ($rows = mysqli_fetch_array($results))
      
      } //if (mysqli_num_rows($results))
      
      else {
          echo "No results to get!";
      }
?>

Here is my all_illinois INSERT table structu

CREATE TABLE IF NOT EXISTS `all_illinois` (
  `state_id` varchar(255) NOT NULL,
  `city_name` varchar(255) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Here is my source table geo_cities structu

CREATE TABLE IF NOT EXISTS `1` (
  `CityId` varchar(255) NOT NULL,
  `CountryID` varchar(255) NOT NULL,
  `RegionID` varchar(255) NOT NULL,
  `City` varchar(255) NOT NULL,
  `Latitude` varchar(255) NOT NULL,
  `Longitude` varchar(255) NOT NULL,
  `TimeZone` varchar(255) NOT NULL,
  `DmaId` varchar(255) NOT NULL,
  `Code` varchar(255) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Here is my code:
// Start MySQL Query for Records
$query = "SELECT codes_update_no_join_1b" .
"SET orig_code_1 = new_code_1,
       orig_code_2 = new_code_2" .
"WHERE concat(orig_code_1, orig_code_2) = concat(old_code_1, old_code_2)";
$results = mysql_query($query) or die(mysql_error());
// End MySQL Query for Records


This query runs perfectly fine when run direct as SQL in phpMyAdmin, but throws this error when running in my script??? Why is this???

Code: [Select]
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '= new_code_1, orig_code_2 = new_code_2WHERE concat(orig_code_1, orig_c' at line 1

Hi, 

can anyone help me I'm new to coding and have been having trouble getting my code to insert into the database. I have checked the connection and that works fine but it says you're registered but it dosen't input it into the database.

This is the code i have in my script:

<?php
echo "<h1>Register</h1>";

$submit = $_POST ['submit'];

//form data
$fullname = strip_tags($_POST ['fullname']);
$username = strip_tags($_POST ['username']);
$password = strip_tags($_POST ['password']);
$repeatpassword = strip_tags($_POST ['repeatpassword']);
$date = date ("Y-m-d");

if ($submit)
{

//check for existance
if ($fullname&&$username&&$password&&$repeatpassword)
{

//check passwords match
if ($password==$repeatpassword)
{

//check char length of username and fullname
if (strlen($username)>25||strlen($fullname)>25)
{
echo "Maximum limit for username and fullname is 25 characters!";
}
else
{

//check password length
if (strlen($password)>25||strlen($password)<6)
{
echo "Your password must be between 6 and 25 characters!";
}
else
{

//register the user

//encrypt password
$password = md5 ($password);
$repeatpassword = md5 ($repeatpassword);

//open database

$connect = mysql_connect ("localhost","","") or die ("couldn't connect!");

mysql_select_db ("phplogin") or die ("couldn't find db!");

mysql_query ("INSERT INTO users VALUES ('','$fullname','$username','$password','$date')");

die ("You've been registered! <a href='index.php'>Click here</a> to return to login page");
}

}

}
else
echo "Your passwords do not match!";


}
else
echo "Please fill in <b>all</b> fields.";

}


?>


<html>
<form action='register.php' method='POST'>
<table>
<tr>
<td>Your Full name:</td>
<td><input type='text' name='fullname' value='<?php echo $fullname ?>'></td>
</tr>
<tr>
<td>Your Username:</td>
<td><input type='text' name='username' value='<?php echo $username ?>'></td>
</tr>
<tr>
<td>Your Password:</td>
<td><input type='password' name='password'></td>
</tr>
<tr>
<td>Repeat your password:</td>
<td><input type='password' name='repeatpassword'></td>
</tr>
</table>
<input type='submit' name='submit' value='Register'>
</form>

</html>

I need to insert a query but with the ID # from another table.  Here is the query I am using to select which works fine.

// get reviews
$strQuery = sprintf("SELECT * FROM messages WHERE intItemID = %d", 
intval($intProductID));
$queryGetReviews = db_query($strQuery);

However my insert doesn't seem to be picking up the %d. 
//query to insert data into table
$sql = "
INSERT INTO messages
SET
firstname = '$name',
message = '$message' WHERE intItemID=%d"
;
$result = mysql_query($sql);
if(!$result)
{
echo "Failed to insert record";
}
else
{
echo "Record inserted successfully";
}
}

Please help!

Basically I need to input data into two tables.

I am running 3 different query's but only 2 of them work. The other one doesn't.

None working query:
mysql_query("INSERT INTO users(username, password, email, pin, key) VALUES('$username', '$password', '$email', '$key', '$pin')");

Working querys:

mysql_query("DELETE FROM beta_keys WHERE keys_new='$key'");

**and**

mysql_query("INSERT INTO beta_keys(keys_used) VALUES('$key')");

So any ideas why the top one doesn't work but the bottom two do?

Hi,

I am creating a new menu (food) in my system.  This consists of a menu, menu_items and menu_connection table.

I can insert the menu name just fine and return its id just fine.

When inserting the menu items, i need to get each of the menu_item_ids to use in the query that inputs the menu_connection.

This is what i have so far:

if ($_SERVER['REQUEST_METHOD']=="POST") {
	/////////////////////
	//menu name insert //
	/////////////////////
	
	$mname = mysqli_real_escape_string($conn, $_POST['newMenuName']);
	$stmt=$conn->prepare('
	                     INSERT IGNORE INTO ssm_menu (menu_name) VALUES (?);
	                     ');
	$stmt->bind_param('s',$mname);
	$stmt->execute();
	$menuInsId = $stmt->insert_id;
	echo $menuInsId;
	$stmt->close();

	/////////////////////
	//menu item insert //
	/////////////////////
	
	$mitname = $_POST['newMenuItem'];
	$stmt=$conn->prepare('
	                     INSERT IGNORE INTO ssm_menu_items (menu_item_name) VALUES (?);
	                     ');
	foreach ($_POST['newMenuItem'] as $k => $nmItem) {
		$mitname = mysqli_real_escape_string($conn, $nmItem);
		$stmt->bind_param('s',$mitname);
		$stmt->execute();
		$menuItmInsId = $stmt->insert_id;
		echo $menuItmInsId;
	}
	$stmt->close();

	///////////////////////////
	//menu connection insert //
	///////////////////////////

	
	$stmt=$conn->prepare('
	                     INSERT IGNORE INTO ssm_menu_connection (menu_id, menu_item_id) VALUES (?,?)
	                     ');
	foreach ($_POST['newMenuItem'] as $k => $nmItem) {
		$stmt->bind_param('ii',$menuInsId, $menuItmInsId);
		$stmt->execute();
		$connectionInserId = $stmt->insert_id;
		echo $connectionInserId;
	}
	$stmt->close();
}

Currently it is inserting each of the items in the connection table with the same id - i understand why but i dont know how to collect up all of the ids to use later

I have this PHP file that I intend to provide the answers to only four questions in a little quiz in a "Questions" table:
QuestionID, QuestionText

Questions:
1 Which is NOT part of the FAT TOM acronym?
2 Of the following choices, which has a better chance of creating a foodborne illness?
3 How should food NEVER be thawed?
4 Where should pesticides be stored?

The answers should go in the Answers table:
"QuestionID, OptionText, CorrectAnswer"

I have hundreds of questions and answers, but I need to get this small sample working first.

The following code runs without triggering an error but it doesn't insert any data.


<?php 
$link = mysql_connect('PathToMyData', 'myUsername', 'MyPassword');  // specifics removed for security

 if (!$link) { 
  die('Could not connect: ' . mysql_error()); 
} 

mysql_select_db('quiz');

$sql = "INSERT INTO ANSWERS (QuestionID,OptionText,CorrectAnswer)
// CorrectAnswer is type BOOL (Correct=1 Wrong Answer=0)

(VALUES (1,'food',0), 
(1,'alkalinity',1),
(1,'time',0),
(1,'temperature',0),
(1,'oxygen',0), 
(1,'moisture',0), 
(2,'celery sticks',0), 
(2,'beef jerky',0), 
(2,'cranberry juice',0), 
(2,'baked potato',1), 
(2,'saltine cracker',0),
(3,'in the refrigerator',0), 
(3,'in a pot in the kitchen at room temperator',1), 
(3,'as a part of the cooking process',0), 
(3,'under cool running water',0), 
(4,'close to the food preparation area for easy access',0), 
(4,'in a locked storage area away from food',1), 
(4,'in the dry storage area',0), 
(4,'in a bin or box under the sink',0);)";

$result = mysql_query($sql);  // executes the query

// close database
mysql_close($link);
?> 


Then I go back to the MySQL admin area at my host's site and query up "SELECT * FROM Answers;" and nothing!  It's still empty! As I said, no error was triggered, but nothing was inserted.  This is discouraging.

my SQL Query wont execute on on following lines:

Code: [Select]
$result = mysql_query("INSERT INTO 'gallery' ('image', 'memberid', 'caption') VALUES ('$newFileName', '$member_id', '$caption')")
    or die (mysql_error());
i get the following error:

Code: [Select]
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''gallery' ('image', 'memberid', 'caption') VALUES ('gallery/9074849_1.jpg', '1',' at line 1
here is my full code:
Code: [Select]
<?php
require_once('connect.php');

$rand = mt_rand(1,9999999);
$rand2 = mt_rand(1,9999999);
$member_id = $_SESSION['SESS_MEMBER_ID'];
$caption = $_POST["caption"];

if(isset($_FILES['uploaded']['name']))
{
$allowed_filetypes = array('.jpg','.gif','.bmp','.png','.jpeg');
$max_filesize = 524288; // Maximum filesize in BYTES (currently 0.5MB) 
    $fileName = basename($_FILES['uploaded']['name']);
$errors = array();
$target = "gallery/";
$fileBaseName = substr($fileName, 0, strripos($fileName, '.'));

   // Get the extension from the filename.
   $ext = substr($fileName, strpos($fileName,'.'), strlen($fileName)-1); 
 
   //$newFileName = md5($fileBaseName) . $ext;
   $newFileName = $target . $rand . "_" . $member_id.$ext;
   
   // Check if filename already exists
   if(file_exists("gallery/" . $newFileName)) 
    {
     $errors[] = "The file you attempted to upload already exists, please try again.";
    }
   // Check if the filetype is allowed.
   if(!in_array($ext,$allowed_filetypes))
    {
         $errors[] = "The file you attempted to upload is not allowed.";
    }
    
// Now check the filesize.
   if(!filesize($_FILES['uploaded']['tmp_name']) > $max_filesize)
    {
         $errors[] = "The file you attempted to upload is too large.";
    }
  
    // Check if we can upload to the specified path.
   if(!is_writable($target))
    {
         $errors[] = "You cannot upload to the specified directory, please CHMOD it to 777.";
    }
 
    //Here we check that no validation errors have occured.
    if(count($errors)==0)
    {

//Try to upload it.
        if(!move_uploaded_file($_FILES['uploaded']['tmp_name'], $newFileName))
        {
            $errors[] = "Sorry, there was a problem uploading your file.";
        }
    }
    //Lets INSERT database information here
    //Here we check that no validation errors have occured.
    if(count($errors)==0)
    {
$result = mysql_query("INSERT INTO 'gallery' ('image', 'memberid', 'caption') VALUES ('$newFileName', '$member_id', '$caption')")
    or die (mysql_error()); 

{
            $errors[] = "SQL Error.";
        }
}
    //If no errors show confirmation message
    if(count($errors)==0)
    {
         echo "<div class='notification success png_bg'>
<a href='#' class='close'><img src='img/cross_grey_small.png' title='Close this notification' alt='close' /></a>
<div>
The file {$newFileName} has been uploaded<br>\n
</div>
</div>";


//echo "The file {$fileName} has been uploaded";
echo "<br>\n";
echo "<a href='gallery.php'>Go Back</a>\n";

   }
    else
    {
        //show error message
        echo "<div class='notification attention png_bg'>
<a href='#' class='close'><img src='img/cross_grey_small.png' title='Close this notification' alt='close' /></a>
<div>
Sorry your file was not uploaded due to the following errors:<br>\n
</div>
</div>";
//echo "Sorry your file was not uploaded due to the following errors:<br>\n";
        echo "<ul>\n";
        foreach($errors as $error)
        {
            echo "<li>{$error}</li>\n";
        }
        echo "</ul>\n";
echo "<br>\n";
echo "<a href='gallery.php'>Go Back</a>\n";
    }
    
}
else
{
    //Show the form
echo "Use the following form below to add a new image to your gallery;<br />\n";
    echo "<form enctype='multipart/form-data' action='' method='POST'>\n";
    echo "Please choose a file: <input name='uploaded' type='file' /><br />\n";
echo "Caption: <input name='caption' type='text' /><br />\n";
    echo "<input type='submit' value='Upload' />\n";
    echo "</form>\n";

//Echo Tests!
    echo "<br /><br />Random FileName: "; 
echo $rand;
echo "<br />";
echo "member ID: #";
echo $member_id;
}
?>
any help appreciated. its prob something simple.

my table has the following fields:

"gallery"
id (primary Key, AUTO_INC)
memberid (fetched from session)
image (will store image name including extension)
caption (from "caption" text field in form)

Hello there,

I am trying to build my site more efficient by sending me error messages that occur. I have decided that most of my errors are mysql errors. Thinking about what to do, I tried to put an insert query in the die message. Here is my line of code below.
Code: [Select]
$globalsql=mysql_query("SELECT * FROM global") or die(' $error=mysql_query("INSERT INTO errors (name, identity, user,) VALUES ('"'Error 1'"', '"'global_functions.php'"', '"'System'"')") ');
However, I get this error:
Parse error: syntax error, unexpected T_CONSTANT_ENCAPSED_STRING in global_functions.php on line 4


Which is that line. Any ideas?

Thanks!

<?php
//$passwordVerify = password_verify($userInput, $passwordInput);
    $con = mysqli_connect("127.0.0.1","root","");     if (!$con)     {         die('Could not connect: ' . mysqli_error());     }       /*if($con){           die('Database connected successfuly.');       }*/     $sql="CREATE DATABASE IF NOT EXISTS 'itech3108_30124248_a1'";               $runquery = mysqli_query($con,$sql);     if(!mysqli_select_db($con, "itech3108_30124248_a1"))     {         die('Database not connected: ' . mysqli_error());     }
    session_start();
    if(isset($_POST['register']))
    {    
        //for image uploading......................
        $files = $_FILES['file'];
        $filename = $files['name'];
        $filrerror = $files['error'];
        $filetemp = $files['tmp_name'];
        $fileext = explode('.', $filename);
        $filecheck = strtolower(end($fileext));
        $fileextstored = array('png' , 'jpg' , 'jpeg');

        if(in_array($filecheck, $fileextstored)){

            $destinationfile = 'img/'.$filename;
            move_uploaded_file($filetemp, $destinationfile);
        }
        $name = $_POST['username'];
        $email = $_POST['email'];
        $confirm = $_POST['confirm'];
        $profile=$_POST['profile'];
       /*$file_ext= null;
        if(isset($_FILES['image'])){
            $errors= array();
            $file_name = $_FILES['image']['name'];
            $file_size =$_FILES['image']['size'];
            $file_tmp =$_FILES['image']['tmp_name'];
            $file_type=$_FILES['image']['type'];
            $file_ext=strtolower(end(explode('.',$_FILES['image']['name'])));
           
            $extensions= array('jpeg','jpg','png');
           
            if(in_array($file_ext,$extensions)=== false){
               $errors[]='extension not allowed, please choose a JPEG or PNG file.';
            }
           
            if($file_size > 2097152){
               $errors[]='File size must be excately 2 MB';
            }
           
            if(empty($errors)==true){
               move_uploaded_file($file_tmp,'img/'.$file_name);
               //echo 'Success';
            }else{
               print_r($errors);
            }
         }
        //$passwordhash = null;
        /*
        if (CRYPT_BLOWFISH == 1)
        {
            $passwordhash = password_hash($password ,PASSWORD_BCRYPT);
        }
        else
        {  
            echo '<script>alert('Blowfish DES not supported.');
            window.location='reg.php';</script>';
        }...............................................................*/
       // die('photo url='. $destinationfile.'name='.$name.'email='.$email.'password='.$password.'profile='.$profile);
        $sql = "INSERT INTO user ('name','email','password','profile') SET ('$name','$email','$confirm','$profile,'$destinationfile')" ;
       // $query="INSERT INTO 'user' ('id','name','email','password','profile','photo') VALUES( ?,?,?,?,?,?) ";
       // $stmt = mysqli_prepare($con, $query) or die(mysqli_error($con));
       // $stmt->bind_param($stmt,'Null',$name,$email,$confirm,$profile,$destinationfile);
        //$stmt->execute();

        //$stmt = mysqli_prepare($con, "INSERT INTO 'user' ('name','email','password','profile','photo') VALUES( ?,?,?,?,?) ");
       // mysqli_stmt_bind_param($stmt, $name,$email,$confirm,$profile,$destinationfile);
       // mysqli_stmt_execute($stmt);
               
        $runquery = mysqli_query($con,$sql);
        //echo 'query'.$runquery;
        if(!$runquery)
        {
           
             die( mysqli_error($con));
        }
        else
        {
            echo '<script>alert("User successfully registered.");
            window.location="login.php";</script>';
        }
    }
    //mysqli_query.close();
    if(isset($_SESSION['name']))
    {
        header('Location:profilepage.php');
    }
?>

<!DOCTYPE html>
<html>
<head>
    <title>Sign Up Page</title>
    <link href='https://fonts.googleapis.com/css?family=Lobster|Montserrat' rel='stylesheet'>
    <link rel='stylesheet' type='text/css' href='stylesheet/register.css'>
</head>

    <header>
        <nav>
            <h1>find Your Love</h1>
            <ul id='navli'>
                <li><a class='homeblack' href='home.php'>Home</a></li>
            </ul>
        </nav>
    </header>
    <style>
        body{
            background-image: url('img/love.jpg');
            background-repeat: no-repeat;
            background-attachment: fixed;
            background-size: cover;}
    </style>
    <div class='divider'></div>
   
        <div class='loginbox'>
        <img src='img/love2.jpg' class='avatar'>
            <h1>Signup </h1>
            <form action='reg.php' method='POST'enctype='multipart/form-data'>
                <p>User Name</p>
                <input type='text' id='username' name='username' placeholder='Enter Username' required='requiored'></p>
               
                <p>Email</p>
                <input type='Email' id='email' name='email' placeholder='Enter email' required='required'>
               
                <p>Password</p>
                <input type='password' id='password' name='password' placeholder='Enter password' required='required'>

                <p>Confirm Password</p>
                <input type='password' id='confirm' name='confirm' placeholder='re-enter password' required='required'>

                <p> upload Image</p>
                <input class="input--style-1" type="file" placeholder="file" name="file">

                <p>Your Words</p>
                <input type='textbox' id='profile' name='profile' placeholder=' your words to describe your self' required='required'>
   
                <input type='submit' name='register' value='Sign Up'>

                <div class='divider'></div>
                <h6><a href='login.php'>Already Have Account ? </a></h6>
            </form>  
        </div>
    </body>
</html>

 

I am having an error with this code

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''name','email','password','profile') SET ('Sasural','kill@1234.com','kill','ANDK' at line 1

I am stuck with this for last 5hrs

Hi I am having troubles inserting item into a table with a primary key in it.   My problem is that every time I added a row into a table without specifying the primary key, mysql will not add it to the next available id.   For example, I have 5 rows initially and I added 10 rows into my table by using the insert into query. However when I delete the 10 rows and add another row in, the id of the additional row will become 16 instead of 6.   FYI, this is a php page that inserts my data into the table.

Hello, I'm having a bit of a problem here, all help to this issues would be much appreciated

I am trying to use text boxes to insert numbers into the database based on what is inputed.
If I have a string, like this for example:
$variable = 09385493;

And I want to insert it into the database like this:
mysql_query("INSERT INTO integers(number)
VALUES ('$variable')");

When checking the integers table in my database, looking at the number field, the $variable that was inserted is outputted as
9385493

Notice the number zero was taken out of the front of the number. If the number is double 0's (009385493), both of those zero's would disappear, too.

Thanks