CODEWITHSUNDEEP
mysqlsqlselectinsert
PhilB
PhilB

Reputation: 27

Mysql select and insert using prepared statement

My mysql query is working fine

INSERT INTO donor_location (pc_id)
SELECT id
FROM pc
WHERE postcode= ?

i.e gets the postcode id from a postcode table then inserts that id into donor_location table. I am using mysqli and prepared statements

without the select part it would be quite easy - something like

$stmt = $mysqli->prepare("INSERT INTO donor_charity(
id) values (?)") ;

however I am completely lost about how to incorporate the select

Upvotes: 1

Views: 2006

Answers (3)

Kao
Kao

Reputation: 2272

What you do is almost the same, just changing the query bit.
To select all records from charity_donor where the id is 25, you would do the follwing query:

SELECT *
FROM   donor_charity
WHERE  id = 25

Now to query this, first you have to prepare it:

$stmt = $mysqli->prepare("
    SELECT *
    FROM   donor_charity
    WHERE  id = ?
");

Now to loop over the results, you must bind the param, and execute the query.

$stmt->bind_param('d', 25 ); // First param means the type of the value you're 
                             passing. In this example, d for digit.
$stmt->execute();

Then you setup an array to hold the data returned from the query,

$row = array();
stmt_bind_assoc($stmt, $row);

And now to loop over the returned data.

while ( $stmt->fetch () ) {
    print_r($row); // Should now contain the column.
}

For documentation, see:
Prepare: http://www.php.net/manual/en/mysqli.prepare.php
Bind param: http://www.php.net/manual/en/mysqli-stmt.bind-param.php
Execute: http://www.php.net/manual/en/mysqli-stmt.execute.php
Fetch: http://www.php.net/manual/en/mysqli-stmt.fetch.php

Upvotes: 2

user2001117
user2001117

Reputation: 3777

You need to use Bind_param after Prepare statement.

$sql = "INSERT INTO donor_charity(
id) values (?)
       ";
/* create a prepared statement */
if (!$stmt = $db->prepare($sql)) {
    echo 'Database prepare error';
    exit;
}

 /* bind parameters for markers */
$stmt->bind_param('ssssss', $id);

$id = '123456';

 /* execute query */
$stmt->execute();

Upvotes: 0

Aboelseoud
Aboelseoud

Reputation: 555

Hope this post helps, it's so simple. http://www.java2s.com/Code/Java/Database-SQL-JDBC/InsertRecordsUsingPreparedStatement.htm

Upvotes: 0

Related Questions