SELECT * FROM in MySQLi

Question 1

My site is rather extensive, and I just recently made the switch to PHP5 (call me a late bloomer).

All of my MySQL query's before were built as such:

"SELECT * FROM tablename WHERE field1 = 'value' && field2 = 'value2'";

This made it very easy, simple and friendly.

I am now trying to make the switch to mysqli for obvious security reasons, and I am having a hard time figuring out how to implement the same SELECT * FROM queries when the bind_param requires specific arguments.

Is this statement a thing of the past?

If it is, how do I handle a query with tons of columns involved? Do I really need to type them all out every time?

Question 2

"SELECT * FROM tablename WHERE field1 = 'value' && field2 = 'value2'";

becomes

"SELECT * FROM tablename WHERE field1 = ? && field2 = ?";

which is passed to the $mysqli::prepare:

$stmt = $mysqli->prepare(
  "SELECT * FROM tablename WHERE field1 = ? && field2 = ?");
$stmt->bind_param( "ss", $value, $value2); 
// "ss' is a format string, each "s" means string
$stmt->execute();

$stmt->bind_result($col1, $col2);
// then fetch and close the statement

OP comments:

so if i have 5 parameters, i could potentially have "sssis" or something (depending on the types of inputs?)

Right, one type specifier per ? parameter in the prepared statement, all of them positional (first specifier applies to first ? which is replaced by first actual parameter (which is the second parameter to bind_param)).

mysqli will take care of escaping and quoting (I think).

Question 3

I went ahead and ran a test where one query uses a prepared statement, and the other builds the entire query then executes that. I'm probably not making what I'm wanting to know easy to understand.

Here's my test code. I was thinking prepared statements sort of held back execution until a $stmt->close() was called to optimize it or something. That doesn't appear to be the case though as the test that builds the query using real_escape_string is at least 10 times faster.

<?php

$db = new mysqli('localhost', 'user', 'pass', 'test');

$start = microtime(true);
$a = 'a';
$b = 'b';

$sql = $db->prepare('INSERT INTO multi (a,b) VALUES(?, ?)');
$sql->bind_param('ss', $a, $b);
for($i = 0; $i < 10000; $i++)
{
    $a = chr($i % 1);
    $b = chr($i % 2);
    $sql->execute();
}
$sql->close();

echo microtime(true) - $start;

$db->close();

?>

Question 4

This actually depends on the Mysql server. The default max size for all data combined in the entire query is 1mb. See: http://dev.mysql.com/doc/refman/5.1/en/packet-too-large.html

If your data combined is under that "max_allowed_packet" threshold, just use "s" for the binding type for any text field. Infact, you can usually get away with using "s" for any field type at all (date, float, etc).

If your entire entry combined that you want to insert is over 1mb (or whatever you reset it to) in length, you'll want to use mysqli_stmt::send_long_data method and the "b" binding type to send this particular field in chunks.

Question 5

If $getOrder has the order details (including company_id), and $getCompany has the company details (including company_id), then you can compare the two.

If they are equal, echo out selected as an attribute in the option, like so:

<select class="form-control" name="company_id" id="company_id">
<?php
    if($getCompany) {

        //Get company ID from Order
        $orderID = $getOrder["company_id"];

        while($company = mysqli_fetch_assoc($getCompany)) { ?>
            <option value="<?php echo $company['company_id']; ?>"
            <?php 
                //Compare and echo `selected` if they are equal
                if($orderId==$company["company_id"]) echo "selected";
            ?>>
                <?php echo $company['company_name']; ?>
            </option>
        <?php }
    } 
?>

That code can be cleaned up, however:

<select class="form-control" name="company_id" id="company_id">
<?php
    if($getCompany) {
        $orderID = $getOrder["company_id"];
        while($company = mysqli_fetch_assoc($getCompany)) {
            echo "<option value='{$company['company_id']}".($getOrder["company_id"]==$getCompany["company_id"] ? " selected" : null).">{$company['company_name']}</option>";
        }
    } 
?>

Question 6

You can simply join both tables:

$q = mysqli_query($db,"SELECT * FROM posts 
LEFT JOIN comments ON comments.username=posts.username 
WHERE comments.username='$username'");

However, it looks like you are not using IDs. I suggest you to create ID auto-increment fields to make the relationship between tables. If you don't understand what I mean, try to follow a tutorial and in an our or two you would have learned more, than just by jumping into coding trying to do things and trying to understand how they work without even knowing if you are doing it right.

SELECT * FROM in MySQLi

Answers

php,mysqli