CODEWITHSUNDEEP
phpmysqlmysql-error-1064
Eos the SeaCat
Eos the SeaCat

Reputation: 57

How to create a table from a query using php and mysql

I am trying to create a file that will drop and recreate a table from the query. I keep getting a syntax error. It has been awhile since I worked in mysql and am not seeing the error. Another set of eyes would be great!

$dbc = @mysqli_connect (DB_HOST, DB_USER, DB_PASSWORD, DB_NAME) OR die ('Could not connect to MySQL: ' . mysqli_connect_error() );  

//drop table for updated database
//Create query
$q = "
DROP TABLE IF EXISTS dbName.tbName;
CREATE TABLE  dbName.tbName (ID int) AS 'SELECT
dbName.tbName.ID
FROM
dbName.tbName
ORDER BY
dbName.tbName.count DESC
LIMIT 8'";
;

//run query
$r = @mysqli_query ($dbc, $q);


//Displays resutls if query ran correctly
if ($r) { // If it ran OK, display the records.

        // Table header.
        echo '<table align="center" >
        <tr><td align="left"><b>Number 1</b></td><td align="left"><b>Number 2</b></td><td align="left"><b>Number 3</b></td><td align="left"><b>Number 4</b></td><td align="left"><b>Number 5</b></td><td align="left"><b>Number 6</b></td>
        <td align="left"><b>Number 7</b></td><td align="left"><b>Number 8</b></td><td align="left"><b>Number 9</b></td><td align="left"><b>Number 10</b></tr>';

        // Fetch and print all the records:
        while ($row = mysqli_fetch_array($r, MYSQLI_ASSOC)) {
                echo '<tr><td align="left">' . $row['Number_1'] . '</td><td align="left">' . $row['Number_2'] . '</td><td align="left">' . $row['Number_3'] . '</td><td align="left">' . $row['Number_4'] . '</td><td align="left">' .
                $row['Number_5'] . '</td><td align="left">' . $row['Number_6'] . '</td><td align="left">' . $row['Number_7'] . '</td><td align="left">' . $row['Number_8'] . '</td>
                <td align="left">' . $row['Number_9'] . '</td><td align="left">' . $row['Number_10'] . '</td></tr>'."\n";
        }

        echo '</table>'; // Close the table.

        mysqli_free_result ($r); // Free up the resources.      

} else { // If it did not run OK.

        // Public message:
        echo '<p class="error">Your numbers could not be retrieved. We apologize for any inconvenience.</p>';

        // Debugging message:
        echo '<p>' . mysqli_error($dbc) . '<br /><br />Query: ' . $q . '</p>';

} // End of if ($r) IF.

mysqli_close($dbc); // Close the database connection.

The error that I am getting is:

Your numbers could not be retrieved. We apologize for any inconvenience.

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 'CREATE TABLE dbName.tbName (ID int) AS 'SELECTdbName.tbNam' at line 2

Query: DROP TABLE IF EXISTS dbName.tbName;
CREATE TABLE dbName.tbName (ID int) AS 'SELECT
dbName.tbName.ID
FROM
dbName.tbName
ORDER BY
dbName.tbName.count DESC
LIMIT 8'

Upvotes: 1

Views: 69

Answers (2)

Ourko1e
Ourko1e

Reputation: 1

A missing blank space between "SELECT" and "dbName.tbName.ID" ?

Upvotes: 0

Professor Abronsius
Professor Abronsius

Reputation: 33813

I think it might be the single quotes around the inner query when building the table.

$q = "
    DROP TABLE IF EXISTS dbName.tbName;
    CREATE TABLE  dbName.tbName (ID int) AS ( SELECT
    dbName.tbName.ID
    FROM
    dbName.tbName
    ORDER BY
    dbName.tbName.count DESC
    LIMIT 8 )";

Upvotes: 0

Related Questions