Pdo Returning Incorrect, But Duplicate, Data. Key's Not in Database

PDO returning incorrect, but duplicate, data. Key's not in database.

It's not duplicates, it's just the current FETCH_MODE you're using. To get as associative keys only you need to specify as such; by default it fetches as both.

Use like so:

$query->fetchAll(PDO::FETCH_NUM); // to fetch with numeric indexes
$query->fetchAll(PDO::FETCH_ASSOC); // to fetch with associative indexes

fetchAll docs

fetch docs

PDO: Remove duplicates of the query's results

The first argument of your fetchAll call schould be PDO::FETCH_ASSOC

See http://php.net/manual/en/pdostatement.fetch.php

PDO query - loop produces duplicate fields?

It looks like you are using the PDO::FETCH_BOTH style.

This will produce an array where the entries are duplicated, once for the column name keys, and once for the integer keys.

See the following for details:

http://php.net/manual/en/pdostatement.fetch.php

Strange PDO results returning a key for each row value

this is configurable behavior:

$statement->fetchAll(PDO::FETCH_ASSOC)

will return associative arrays -- see argument $fetch_style.

you can configure your pdo instance to always do this:

$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);

there also is PDOStatement::setFetchMode() which is useful when using repeated prepared statements.

check for duplicate entry vs use PDO errorInfo result

INSERT + check status should be a better approach. With SELECT + INSERT you can have another thread insert the same value between the SELECT and the INSERT, which means you would need to also wrap those two statements in a table lock.

It is easy to err on the side of too much defense in your coding. Python has the saying that "it is easier to ask for forgiveness than to ask for permission", and this philosophy is not really Python-specific.

PDO Query fetchAll remove keys but keep values including duplicates

Are you ok with associative arrays? Then create a new array (say $def_presentation or something ) and go for something like

foreach($presentation as $row) {
    if(!isset($def_presentation[$row['presentation_uid']])) {
        $def_presentation[$row['presentation_uid']] = $row;
        unset($def_presentation[$row['presentation_uid']]['customfield_name'],$def_presentation[$row['presentation_uid']]['customfield_data_value']);
        $def_presentation[$row['presentation_uid']]['customfields'] = array();
    } 
    $def_presentation[$row['presentation_uid']]['customfields'][$row['customfield_name']] = $row['customfield_data_value'];
}

PDO returns wrong value

The line

if ($password_verified = true) {

is very incorrect because you're basically just assigning true to $password_verified. You should just be doing a if($password_verified) though, I am not sure if it will solve your problem.

You are also not parsing the results as you should be using fetchAll() and then going through the results to see if the user exists.

PDO Insert on Duplicate Key Update

What you have attempted to do is to dynamically build a SQL string that will become parameterized. The :paramname parameters are expected to be single values mapped to column values, where clause parameters, etc. Instead you have used $fields[] = sprintf("%s = :%s", $key, $key); to create a string of :paramname fields in order to plug into the query. This just won't work in a parameterized statement.

Rather than doing ON DUPLICATE KEY UPDATE :fieldlist, you should build the whole sql string before passing it into prepare().

Then rather than use the bindParam() method to bind each one individually, you can use an alternate syntax to execute() to pass in an array of expected parametric values. They need to be in the correct order, or have array keys the same names as the :param parameters in your SQL. See the docs for more info and examples.

$array_of_parameters = array();
foreach($faculty as $key=>$val){
    $array_of_parameters[$key] = $val);
}
$stmt->execute($array_of_parameters);

EDIT To properly use parameters in your UPDATE statement, do it like this:

// Create your $field_list before attempting to create the SQL statement
$field_list = join(',', $fields);

$update = 'UPDATE fhours SET '.$field_list. 'WHERE fname=:fname AND lname=:lname';
// Here, echo out $update to make sure it looks correct

// Then add the fname and lname parameters onto your array of params
$array_of_parameters[] = $_POST['fname'];
$array_of_parameters[] = $_POST['lname'];

// Now that your parameters array includes all the faculty in the correct order and the fname & lname,
// you can execute it.
$stmt->prepare($update);
$stmt->execute($array_of_parameters);

PDO query duplicate one bind Instead of their own

send your data right into execute. without using bindParam

$dbh_query = $dbh->prepare($query);
$dbh_query->execute($column);

but you have to understand that all this spaghetti

''.$query.' '.$table.' SET '.implode(", ",$query_array).' WHERE '.$query_array_id.''

is severely vulnerable to SQL injection

MySQL issue on INSERT ... SELECT ON DUPLICATE KEY UPDATE and LAST_INSERT_ID()

DEMO:

CREATE TABLE test (id INT AUTO_INCREMENT PRIMARY KEY, 
                   category INT, 
                   value INT,
                   UNIQUE (category, value) );

CREATE TRIGGER tr_ai
AFTER INSERT ON test
FOR EACH ROW 
SET @ids_array := CONCAT_WS(',', @ids_array, NEW.id);

CREATE TRIGGER tr_au
AFTER UPDATE ON test
FOR EACH ROW 
SET @ids_array := CONCAT_WS(',', @ids_array, NEW.id);

SET @ids_array := NULL;
INSERT INTO test (category, value)
VALUES (1,11), (2,22);
SELECT * FROM test;
SELECT @ids_array;

id | category | value
-: | -------: | ----:
 1 |        1 |    11
 2 |        2 |    22

| @ids_array |
| :--------- |
| 1,2        |

SET @ids_array := NULL;
INSERT INTO test (category, value)
VALUES (1,111), (2,22)
ON DUPLICATE KEY 
UPDATE value = NULL;
SELECT * FROM test;
SELECT @ids_array;

id | category | value
-: | -------: | ----:
 1 |        1 |    11
 3 |        1 |   111
 2 |        2 |  null
| @ids_array |
| :--------- |
| 3,2        |

-- do not reset @ids_array
INSERT INTO test (id, category, value)
VALUES (1,4,44), (22,2,22)
ON DUPLICATE KEY 
UPDATE value = NULL;
SELECT * FROM test;
SELECT @ids_array;

id | category | value
-: | -------: | ----:
 1 |        1 |  null
 3 |        1 |   111
 2 |        2 |  null
22 |        2 |    22

| @ids_array |
| :--------- |
| 3,2,1,22   |

db<>fiddle here

---

Related Topics