array in mysqli query

[westman]

$uniqueValueArray[] = $mainRow; // my array

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

How do I make a query look for an array?


 

[westman]

Is it even possible to run an array through a query?

[westman]

$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?

[Ingolme]

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);
}

 

[westman]

Thank you for your support.
I really appropriate it.

I am having a problem with this line...
while($row = $result->fetch(PDO::FETCH_ASSOC)) {
 

 

[iwato]

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

[westman]

using....
echo $result;
my output is 1

[Ingolme]

I made a mistake, the fetch() method is called on the statement itself

while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {

 

[westman]

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 May 27, 2018 by westman

[Ingolme]

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.

 

[westman]

thank you for the $row info.
it tried...
$limit = 'LIMIT ' .($pagenum - 1) * $page_rows .',' .$page_rows;
but got the following error

[Ingolme]

You should print out the entire query and analyze it for yourself to see where the error is.

[westman]

the output of
$limit = 'LIMIT ' .($pagenum - 1) * $page_rows .',' .$page_rows;
echo "<br /> limit: $limit <br />";
is...
limit: LIMIT 190,10 

[Ingolme]

Print out the entire SQL string, not just the limit part.

I don't know what's wrong, but I do know how to find out. 

[westman]

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

[westman]

I removed one LIMIT and it is okay now.

how do I close the PDO query and and connection?  

[dsonesuk]

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

[westman]

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?

[dsonesuk]

Here's an idea, Try it!