Jump to content
westman

array in mysqli query

Recommended Posts

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


 

Share this post


Link to post
Share on other sites
$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?

Share this post


Link to post
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

Share this post


Link to post
Share on other sites

Thank you for your support.
I really appropriate it.

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

 

Capture.PNG

Share this post


Link to post
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

Share this post


Link to post
Share on other sites

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

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

 

  • Like 1

Share this post


Link to post
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

Share this post


Link to post
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

Share this post


Link to post
Share on other sites

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

Capture.PNG

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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. 

Share this post


Link to post
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

Share this post


Link to post
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

Share this post


Link to post
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?

Share this post


Link to post
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

×