why cant i change my old methode for a new one?

Question

i got an db that i can add stuff in it with this sql statement using pdo type of connection .. :

$sql_createAD = "INSERT INTO `kijilikedb`.`advertise` (`AD_ID`, `AD_NAME`, `REF_STATE`, `REF_USER`, `REF_CAT`, `REF_SUB`, `REF_DESC`, `REG_DATE`, `EXP_DATE`, `AD_TYPE`, `AD_PRICE` , `IMAGE`) VALUES (NULL,'".$_POST['Title']."','".$_POST['state']."','',".$currentCAT.",'".$_POST['sub']."', ".$currentDescId.", '".$today."', '".$EXP."','".$_POST['type']."','".$_POST['Price']."','".mysql_real_escape_string(file_get_contents($_FILES['image']['tmp_name']))."')";
$con->query($sql_createAD );

in big ..... im adding an article that contain information and everyting .... but when im adding the img to the database im using ".mysql_real_escape_string" that is a depreciated methode that i should use anymore ...... so now i want to replace it ... but i read there is no alternative for this in pfo ...... but im shure i can find an work around it ..... so plz help me finding it! :D

i find that maybe if im using an $con->prepare() for the insert and execute() for puting it in the db it could work ... but in doest for me ..... the error i get when i doing is : SQLSTATE[HY093]: Invalid parameter number: mixed named and positional parameters

look at the try i did :

$query = "INSERT INTO `kijilikedb`.`advertise` (`AD_ID`, `AD_NAME`, `REF_STATE`, `REF_USER`, `REF_CAT`, `REF_SUB`, `REF_DESC`, `REG_DATE`, `EXP_DATE`, `AD_TYPE`, `AD_PRICE` , `IMAGE`) VALUES (NULL,'".$_POST['Title']."','".$_POST['state']."','',".$currentCAT.",'".$_POST['sub']."', ".$currentDescId.", '".$today."', '".$EXP."','".$_POST['type']."','".$_POST['Price']."','".file_get_contents($_FILES['image']['tmp_name'])."')";
$preparedQuery = $con->prepare($query);
$preparedQuery->execute();

You need to read a tutorial about PDO and try to understand the basic concepts. — JJJ, Oct 23 '14 at 19:38
*"but i read there is no alternative"* - The alternative is [**prepared statements**](http://php.net/pdo.prepared-statements). — Funk Forty Niner, Oct 23 '14 at 19:38
This question appears to be off-topic because it is about not reading the documents on PDO using prepared statements. [**PDO with prepared statements**](http://php.net/pdo.prepared-statements) — Funk Forty Niner, Oct 23 '14 at 19:40

score 0 · Accepted Answer · answered Oct 23 '14 at 20:01

Here's how I would do it with a prepared statement with parameters:

$query = "INSERT INTO kijilikedb.advertise
    SET AD_ID = :ad_id,
        AD_NAME = :ad_name,
        REF_STATE = :ref_state,
        REF_USER = :ref_user,
        REF_CAT = :ref_cat, 
        REF_SUB = :ref_sub,
        REF_DESC = :ref_desc,
        REG_DATE = :reg_date,
        EXP_DATE = :exp_date,
        AD_TYPE = :ad_type,
        AD_PRICE = :ad_price,
        IMAGE = :image";

This uses an alternative syntax for INSERT. It's a bit easier to read and easier to match up your column names with your query parameter placeholders. But this syntax doesn't support multi-row inserts.

Next, create an associative array with your values. The keys match the parameter placeholder names above (the leading colon character is not required).

When you use query parameters, you must not use any escape-string method (FWIW, PDO::quote() does escaping, but adds the quote marks as well).

$values = array(
    'ad_id' => NULL,
    'ad_name' => $_POST['Title'],
    'ref_state' => $_POST['state'],   
    'ref_user' => '',
    'ref_cat' => $currentCAT, 
    'ref_sub' => $_POST['sub'],
    'ref_desc' => $currentDescId,
    'reg_date' => $today,
    'exp_date' => $EXP,
    'ad_type' => $_POST['type'],
    'ad_price' => $_POST['Price'],
    'image' => file_get_contents($_FILES['image']['tmp_name'])
);

Then finally, the prepare/execute is a simple two lines of code:

$preparedQuery = $con->prepare($query);
$preparedQuery->execute($values);

why cant i change my old methode for a new one?

1 Answers1