Asked  2 Years ago    Answers:  5   Viewed   46 times

I'm playing around with MySQLi at the moment, trying to figure out how it all works. In my current projects I always like to echo out a query string while coding, just to make sure that everything is correct, and to quickly debug my code. But... how can I do this with a prepared MySQLi statement?

Example:

$id = 1;
$baz = 'something';

if ($stmt = $mysqli->prepare("SELECT foo FROM bar WHERE id=? AND baz=?")) {
  $stmt->bind_param('is',$id,$baz);
  // how to preview this prepared query before acutally executing it?
  // $stmt->execute();
}

I've been going through this list (http://www.php.net/mysqli) but without any luck.


EDIT

Well, if it's not possible from within MySQLi, maybe I'll stick with something like this:

function preparedQuery($sql,$params) {
  for ($i=0; $i<count($params); $i++) {
    $sql = preg_replace('/?/',$params[$i],$sql,1);
  }
  return $sql;
}

$id = 1;
$baz = 'something';

$sql = "SELECT foo FROM bar WHERE id=? AND baz=?";

echo preparedQuery($sql,array($id,$baz));

// outputs: SELECT foo FROM bar WHERE id=1 AND baz=something

Far from perfect obviously, since it's still pretty redundant — something I wanted to prevent — and it also doesn't give me an idea as to what's being done with the data by MySQLi. But I guess this way I can quickly see if all the data is present and in the right place, and it'll save me some time compared to fitting in the variables manually into the query — that can be a pain with many vars.

 Answers

1

I don't think you can - at least not in the way that you were hoping for. You would either have to build the query string yourself and execute it (ie without using a statement), or seek out or create a wrapper that supports that functionality. The one I use is Zend_Db, and this is how I would do it:

$id = 5;
$baz = 'shazam';
$select = $db->select()->from('bar','foo')
                       ->where('id = ?', $id)
                       ->where('baz = ?', $baz); // Zend_Db_Select will properly quote stuff for you
print_r($select->__toString()); // prints SELECT `bar`.`foo` FROM `bar` WHERE (id = 5) AND (baz = 'shazam')
Saturday, August 13, 2022
1

The display system you are looking for is more complex than it seems, because if you have 58 pages and your current page is 53, you'll want to show that three-dot button before page 53 and not after, like this:

1 2 ... 50 51 52 [53] 54 55 56 57 58

And when your current page is 20, you'll want them to appear on both sides:

1 2 ... 18 19 [20] 21 22 23 ... 57 58

In general, you want a system whereby a maximum number of labels/buttons is output (it seems to be 12 in your example), also counting the three-dot labels.

It would be user-friendly to show at least the two pages preceding the current page, and the two following it.

You could write a function to generate a list of page numbers that follow these rules, and use the number 0 to denote the three-dot button:

// Returns an array of $max_length (or less) page numbers
// where a 0 in the returned array denotes a gap in the series.
// Parameters:
//   $tpages:     total number of pages
//   $page:       current page
//   $max_length: maximum size of returned array
function getPageList($tpages, $page, $max_length) {
    if ($tpages <= $max_length) {
        // no breaks in list
        return range(1, $tpages);
    }
    if ($page <= $max_length - 5) {
        // no break on left of page
        return array_merge(range(1, $max_length-3), array(0, $tpages-1, $tpages));
    }
    if ($page >= $tpages - $max_length + 6) {
        // no break on right of page
        return array_merge(array(1, 2, 0), range($tpages - $max_length + 4, $tpages));
    }
    // breaks on both sides
    $size = $max_length - 6;
    $first = $page - floor(($size-1)/2);
    return array_merge(array(1, 2, 0), range($first, $first + $size - 1), 
                       array(0, $tpages-1, $tpages));
}

If you call this function like this:

getPageList(58, 53, 12)

It will return this:

[1, 2, 0, 50, 51, 52, 53, 54, 55, 56, 57, 58]

Or like this, changing the current page number:

getPageList(58, 20, 12)

It will return this:

[1, 2, 0, 18, 19, 20, 21, 22, 23, 0, 57, 58]

Notice the zeroes, which are the place holders for the three-dot buttons.

Now the difficult part has been done. You can call that function from your existing code without having to change much. Instead of your for loop, you'll use a foreach on the output of the above function. In the loop you'll have an extra test on the zero, so you can output the three-dot label:

$output = "";
// first part of your code for getting $tpages and 
// generating prev/next buttons comes here: it does not change
// ...
// 
$output .= '<div class="fpagination fpagination-centered"><ul>';
foreach (getPageList($tpages, $page, 12) as $i)
{
    if ($i == $page)
    {
        $output .= '<li class="active"><a href="/articles/' . $seo . '&p=' . $i . 
                     '#comments" ng-click="progress()" class="goToTop">' . 
                     $i . '</a></li>';
    }
    else if ($i == 0)
    {
        $output .= '<li>&hellip;</li>';
    }
    else
    {
        $output .= '<li><a href="/articles/' . $seo . '&p=' . $i . '#comments" 
                    ng-click="progress()" class="goToTop">' . $i . '</a></li>';
    }
}
$output .= '</ul></div>';           
echo $output;

Notice that the last argument to the getPageList function is 12. You can configure this number to your needs. It depends on how wide you want your output to become.

Tuesday, October 11, 2022
5

I worked on the Zend_Db_Adapter_Mysqli and Zend_Db_Statement_Mysqli classes quite a bit to get this to work, since we wanted to make it conform to the PDO and PDOStatement interface. It was pretty laborious, because of the confusing way MySQLi insists on making you bind variables to get results, and the variety of fetch modes supported by PDOStatement.

If you want to see the code in Zend_Db, pay special attention to the functions Zend_Db_Statement_Mysqli::_execute() and fetch(). Basically, the _execute() method binds an array of variable references using call_user_func_array(). The tricky part is that you have to initialize the array so the bind_result() function gets the references. Uh, that wasn't totally clear, so go take a look at the code.

Or else just use PDO's MySQL driver. That's what I would do in your shoes.

Monday, August 15, 2022
1

yo need create the user "pma" in mysql or change this lines(user and password for mysql):

/* User for advanced features */
$cfg['Servers'][$i]['controluser'] = 'pma'; 
$cfg['Servers'][$i]['controlpass'] = '';

Linux: /etc/phpmyadmin/config.inc.php

Sunday, August 14, 2022
3

This is how your code should look (with added SQL Injection protection):

<?php
include "dbinfo.php"; //contains mysqli_connect information (the $mysqli variable)
//inputs
$name = mysqli_real_escape_string($_GET['name']);
$text = mysqli_real_escape_string($_GET['text']);

$sqlqr = "INSERT INTO `ncool`.`coolbits_table` (`name`, `text`, `date`) VALUES ('" . $name . "', '" . $text . "', CURRENT_TIMESTAMP);";

mysqli_query($mysqli,$sqlqr); //function where the magic happens.
?>

Take a look at what I've done. Firstly I've escaped the user input you're retrieving into the $name and $text variables (this is pretty much a must for security reasons) and as others have suggested you should preferably be using prepared statements.

The problem is that you weren't surrounding string values with single quotes ('), which is a requirement of the SQL syntax.

I hope this helps to answer your question.

Monday, September 12, 2022
 
Only authorized users can answer the search term. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :
 
Share

Browse Other Code Languages