Jump to content

array in mysqli query


westman
 Share

Recommended Posts

$seach = '';
foreach ($uniqueValueArray_id as $key => $value) {
$seach .= 'id='.$value;
$seach .= ' OR ';
}
$seach = rtrim($seach,' OR ');
echo $seach;

$sql = "SELECT id, text, date, FROM text WHERE $seach ORDER BY id DESC $limit";
$query = mysqli_query($conn, $sql);

I am getting closer but I do have a problem with the query still.
Any help?

Link to comment
Share on other sites

First, you have to use prepared statements to send data to your queries. If you don't it's highly likely that some unexpected data will cause errors in your query and break your program and in the worst case scenario this can be used to hack your website.

You can use the IN() operator to compare a field to multiple values. This works best in PDO, where you can pass parameters as arrays:

<?php
$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);

// Set the PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// Build a string of placeholders like ?,?,?,?, one for each array element.
$amount = count($uniqueValueArray_id);
$placeholders = array_fill(0, $amount, '?');
$search = implode(',', $placeholders);

// Prepare the statement
$limit = 5; // Make absolutely sure this value is an integer or the program will break
$stmt = $conn->prepare("SELECT id, text, date, FROM text WHERE id IN ($search) ORDER BY id DESC LIMIT $limit");

// Run the statement, passing in the values
$result = $stmt->execute($uniqueValueArray_id);

while($row = $result->fetch(PDO::FETCH_ASSOC)) {
  // Display the result here however you want
  print_r($row);
}

 

  • Like 1
Link to comment
Share on other sites

Make sure that $result is not empty.

Enter

$result = $stmt->execute($uniqueValueArray_id);

below

$result = $stmt->execute($uniqueValueArray_id);

and make sure that $result is not empty.  Also make sure that you are using the proper fetch-style.  I do not use PDO, so I cannot help you any further.

Roddy

Link to comment
Share on other sites

works well.
thank you.
last one. How do I get a variable out the while loop and how do I close the DB connection?

Also, is it possible for
$limit = 5;
to be
$limit = "34,5";
as it is a pagination script.

PDO is new to me.

Edited by westman
Link to comment
Share on other sites

In the loop, the variable $row is an associative array that contains all the values that you need. The $row variable works exactly the same as it does in this example: https://www.w3schools.com/php/showphpfile.asp?filename=demo_db_select_oo

For the LIMIT part, it doesn't matter if you're using PDO or anything else, you just need to put the two numbers in the query. You can have two variables: $offset and $limit and then set the query to be "LIMIT $offset, $limit". Make sure that $offset and $limit are both forced to be integers or you'll be facing the problem of SQL injection again.

 

  • Like 1
Link to comment
Share on other sites

this is my output from print_r($stmt);.
PDOStatement Object ( [queryString] => SELECT id, main, id_from, id_to, pm_subject, pm_text, pm_date, pm_open FROM text WHERE id IN (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) ORDER BY id DESC LIMIT LIMIT 0,10 ) 
Fatal error: Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 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 'LIMIT 0,10' at line 1 in /home/outlinetalent/public_html/t_all.php:86 Stack trace: #0 /home/mysite/public_html/file.php(86): PDOStatement->execute(Array) #1 {main} thrown in /home/mysite/public_html/file.php on line 86

Link to comment
Share on other sites

IF only there was some where that I could search and the relevant pages would show in a listing of some kind? That would be great! but! Never going to happen in my lifetime.

http://php.net/manual/en/pdo.connections.php

https://www.w3schools.com/php/php_mysql_connect.asp

Link to comment
Share on other sites

Thank you.

Last question.
If I have my usual
$conn = new mysqli($db_host, $db_username, $db_pass, $db_name);
in an external file would it be a problem if I add
$conn_pdo = new PDO("mysql:host=$servername2;dbname=$dbname2", $username2, $password2);
in the same file?

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...