Two query in mysqli prepared?

Question

I am executing two queries in one mysqli connection, the first one is working fine, but the second one is not.

Here's code:

<?php
//from server
$con = mysqli_connect("localhost","user","pass","db");

// Check connection
if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }
?>


<?php

//mysqli prepare
$stmt = $con->prepare("INSERT INTO users_rooms (ip, rooms, description, date_time)
        VALUES (?,?,?, NOW())");
//bind params
$stmt->bind_param("sss", $ip, $rooms, $description);

    $trunc_text = substr($_POST['channel_description'], 0, 250);
    $rooms = preg_replace('/[^\w\d\s]+/', "", $_POST['channel_name']);
    $temp_rooms = str_replace(' ', '_', $rooms);
    $con->error;
    if(($trunc_text < 251) && isset($rooms)){

        //insert and execute msyqli
        $ip = $_SERVER['REMOTE_ADDR'];
        $rooms = mysqli_real_escape_string($con, htmlspecialchars(strip_tags($temp_rooms)));
        $description = mysqli_real_escape_string($con, htmlspecialchars(strip_tags($trunc_text)));
        $stmt->execute();
    }

//The above part of mysql is working fine, it's inserting the data as expected

//The below part of mysql is not working, it's not creating a table as $rooms

$rooms = mysqli_real_escape_string($con, htmlspecialchars(strip_tags($temp_rooms)));

$sql = "CREATE TABLE '$rooms' (
                        id BIGINT(255) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                        ip VARCHAR(255) NOT NULL,
                        time VARCHAR(255) NOT NULL,
                        image_path VARCHAR(255),
                        smiley_path VARCHAR(255),
                        text_input VARCHAR(255),
                        current_date DATETIME
                        )";
$con->query($sql);
$con->error;
$stmt->close(); 
$con->close();
?>

Does `$rooms` evaluate to something usable? Try it without the quote marks too. — ethrbunny, Dec 23 '14 at 13:58
well, it must not mean it's not working, it's just that it does something differently than you expected. You need to check for the MYSQL error that it creates. Please see [Turning query errors to Exceptions in MySQLi](http://stackoverflow.com/q/14578243/367456) and [How to get useful error messages in PHP?](http://stackoverflow.com/q/845021/367456) - for example the identifier quote character you use is wrong, double check with the documentation: https://dev.mysql.com/doc/refman/5.0/en/identifiers.html — hakre, Dec 23 '14 at 13:59

Ram Sharma · Accepted Answer · 2015-02-09T06:51:19.000

2

You have error in your sql query, try below code

$sql = "CREATE TABLE $rooms (
    `id` BIGINT(255) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `ip` VARCHAR(255) NOT NULL,
    `time` VARCHAR(255) NOT NULL,
    `image_path` VARCHAR(255),
    `smiley_path` VARCHAR(255),
    `text_input` VARCHAR(255),
    `current_date` DATETIME
)";

edited Feb 09 '15 at 06:51

answered Dec 23 '14 at 14:03

Ram Sharma

8,676
7
43
56

I copied and pasted your code, and it's not working either. I have doubt, whether CREATE TABLE works this way? – Yogie Dec 23 '14 at 14:09
have your replace the `$room` with any other word, try above table code again – Ram Sharma Dec 23 '14 at 14:10
Replacing $room with room, works fine! – Yogie Dec 23 '14 at 14:14
I found it, it's not taking the value of $rooms, I did an echo $rooms, and that echoed the correct value of $rooms, but $rooms in a query is not echoing its value. Something seems to be wrong with syntax. – Yogie Dec 23 '14 at 14:19
syntax as well as table creation code – Ram Sharma Dec 23 '14 at 14:20
Can you suggest the right code please? with explanation if convenient. – Yogie Dec 23 '14 at 14:22
try above sql first, if it gives you error than share that error again – Ram Sharma Dec 23 '14 at 14:31
The above code works, thanks a lot bro!! I'll accept your answer. – Yogie Dec 23 '14 at 14:37
*"You are using reserve word `time`"* - `time` isn't a reserved word http://dev.mysql.com/doc/refman/5.5/en/reserved-words.html – Funk Forty Niner Feb 08 '15 at 04:05

Two query in mysqli prepared?

1 Answers1