INSERT mysql_num_rows as a variable

Question

I am creating a CMS in in which when you ADD NEW PAGE, a display_order will automatically grab the next highest number according to the number of rows already present. Here's what I currently have:

<?php

if(isset($_POST['updateContent'])){

    require ("connection.php");
    $sql = "SELECT * FROM pages";
    $result = $conn->query($sql) or die(mysqli_error());

    $content = $_POST['content'];
    $title = $_POST['title'];
    $id = $_POST['id'];

    $order = mysqli_num_rows($result);

    if (empty($id)){
        /** ADD NEW SLIDE*/
        $sql = "INSERT INTO pages (title, content, display_order, visible) VALUES ('".$title."', '".$content.", '".$order.", 0)";
    }else{
        /** UPDATE SLIDE*/
        $sql = "UPDATE pages SET content = '".$content."', title = '".$title."' WHERE id = '".$id."'";
    }

    if ($result){
        header("Location: admin.php");
    }
}

?>

What this code is doing is taking the HTML form that I'm using in a page called edit.php and determining if it is new page or simply a page that is being updated. The error that I am getting is that NOTHING is posting to the database at all. If I remove the $sql, $result and $order lines.. the script works fine, but the display_order variable will not be set to the next highest number.

// comment the header line. Then post what you see – Apr 08 '13 at 16:13 — , Apr 08 '13 at 16:13

score 1 · Accepted Answer · answered Apr 08 '13 at 16:16

There is an error in your query:

INSERT INTO pages (title, content, display_order, visible)
VALUES ('".$title."', '".$content.", '".$order.", 0)";
                                     ^-- here

Should be:

INSERT INTO pages (title, content, display_order, visible)
VALUES ('".$title."', '".$content."', ".$order.", 0)";
                                   ^-- quote goes here

Also, using mysqli doesn't magically protect you from SQL-insertion. Escape dat input!

score 0 · Answer 2 · answered Apr 08 '13 at 16:26

The common way to solve the situation is to use AUTO_INCREMENT field in pages table. Sequentially insert and then ask for LAST_INSERT_ID

php way: http://php.net/manual/en/function.mysql-insert-id.php

native mysql way: http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_last-insert-id

INSERT mysql_num_rows as a variable

2 Answers2