westman Posted May 27, 2018 Share Posted May 27, 2018 $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? Link to comment Share on other sites More sharing options...
westman Posted May 27, 2018 Author Share Posted May 27, 2018 Is it even possible to run an array through a query? Link to comment Share on other sites More sharing options...
westman Posted May 27, 2018 Author Share Posted May 27, 2018 $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 More sharing options...
Ingolme Posted May 27, 2018 Share Posted May 27, 2018 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); } 1 Link to comment Share on other sites More sharing options...
westman Posted May 27, 2018 Author Share Posted May 27, 2018 Thank you for your support. I really appropriate it. I am having a problem with this line... while($row = $result->fetch(PDO::FETCH_ASSOC)) { Link to comment Share on other sites More sharing options...
iwato Posted May 27, 2018 Share Posted May 27, 2018 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 More sharing options...
westman Posted May 27, 2018 Author Share Posted May 27, 2018 using.... echo $result; my output is 1 Link to comment Share on other sites More sharing options...
Ingolme Posted May 27, 2018 Share Posted May 27, 2018 I made a mistake, the fetch() method is called on the statement itself while($row = $stmt->fetch(PDO::FETCH_ASSOC)) { 1 Link to comment Share on other sites More sharing options...
westman Posted May 27, 2018 Author Share Posted May 27, 2018 (edited) 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 Link to comment Share on other sites More sharing options...
Ingolme Posted May 27, 2018 Share Posted May 27, 2018 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. 1 Link to comment Share on other sites More sharing options...
westman Posted May 27, 2018 Author Share Posted May 27, 2018 thank you for the $row info. it tried... $limit = 'LIMIT ' .($pagenum - 1) * $page_rows .',' .$page_rows; but got the following error Link to comment Share on other sites More sharing options...
Ingolme Posted May 28, 2018 Share Posted May 28, 2018 You should print out the entire query and analyze it for yourself to see where the error is. Link to comment Share on other sites More sharing options...
westman Posted May 28, 2018 Author Share Posted May 28, 2018 the output of $limit = 'LIMIT ' .($pagenum - 1) * $page_rows .',' .$page_rows; echo "<br /> limit: $limit <br />"; is... limit: LIMIT 190,10 Link to comment Share on other sites More sharing options...
Ingolme Posted May 28, 2018 Share Posted May 28, 2018 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. Link to comment Share on other sites More sharing options...
westman Posted May 30, 2018 Author Share Posted May 30, 2018 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 More sharing options...
westman Posted May 30, 2018 Author Share Posted May 30, 2018 I removed one LIMIT and it is okay now. how do I close the PDO query and and connection? Link to comment Share on other sites More sharing options...
dsonesuk Posted May 30, 2018 Share Posted May 30, 2018 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 More sharing options...
westman Posted May 30, 2018 Author Share Posted May 30, 2018 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 More sharing options...
dsonesuk Posted May 30, 2018 Share Posted May 30, 2018 Here's an idea, Try it! 1 Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now