MySQL Prepared Statement - Combinging 3 seperate statements in to 1 statement

Question

I'm currently learning MySQL about prepared statements and I was wondering if this code I've written is the most efficient way.

The process:

1. There's a query that checks a table called 'websites' for a field called websites_pagecount.

   The same query then increments the field by 1. (I haven't done this is the database using auto-increment because there will be more than one row with the same number.)

2. There's a query that then runs and grabs the new number.

3. Then another query runs and creates a row in a table called pages and sets the new pagecount number in to the page_id field (again, I haven't used auto-increment on this as there will be more than one row with the same number).

However, it all seems a bit 'cumbersome'.

$mysql['stmt'] = $mysql['conn']->prepare("UPDATE websites SET website_pagecount=website_pagecount+? WHERE website_domain=?");
$mysql['stmt']->bind_param("is", $a = 1, $website['website_id']);
$result = $mysql['stmt']->execute();
$mysql['stmt']->close();


$mysql['stmt'] = $mysql['conn']->prepare("SELECT website_pagecount FROM websites WHERE website_domain = ?");
$mysql['stmt']->bind_param("s", $website['website_id']);
$result = $mysql['stmt']->execute();
//GET ROWS
$result = $mysql['stmt']->get_result();
$pagecount = $result->fetch_assoc();
$mysql['stmt']->close();


$mysql['stmt'] = $mysql['conn']->prepare("INSERT INTO pages (page_id,page_parent, page_title, page_description,page_keywords, website_id, page_layout) VALUES (?,?,?,?,?,?,?)");
$mysql['stmt']->bind_param("issssss",$pagecount['website_pagecount'],$item['parentpage'],$item['pagetitle'],$item['pagedescription'],$item['pagekeywords'],$website['website_id'],$item['pagelayout'] );
$result = $mysql['stmt']->execute();
$mysql['stmt']->close();

The code is used to create a new web page from a web based administration panel. The admin panel can be used to edit multiple websites (depending on which user is logged in) All website information is held in the 'websites' table and all the pages from all the websites are held in the 'pages' table.

Every page has an auto-incrementing unique ID, however they also have a page id relative to the website they belong to. IE (test.com can have pages with ids 1,2 and 3 however testagain.com can also have pages with ids 1,2,3)

The reason for the 'website_pagecount' field is so when a new page is created for a particular website, it counts up in order.

The reason I have done this is because if i used a 'row count' function, for example, to generate the page ID field, and pages with the ID's 1 & 3 got deleted, when a new page was generated it would set the ID to '2' - however that would conflict with the already existing page that has an ID set which was 2.

If there was to be 30-40 pages for a website and pages got deleted and created over time, there would be an awful lot of conflicts eventually per website.

Answer 1

Thanks for the comments. I understand what your webpage does now.

Suggestion #1 - Descriptions Of What Your Code Does - You might want to create some functions (or add some comments) just to make it more clear what each segment of code does. The OOP people like to make tons of functions that describe chunks of code, and the non-OOP people like comments. You can go either way, in my opinion. In my example here, I will use functions.

Suggestion #2 - More Whitespace - I would also add some spaces after your commas and maybe some enters after your commas in your bind_param() functions so that everything wraps nicely and is easy to read. You can do the same thing with your prepare() functions. It isn't a big deal for single line SQL statements like "SELECT * FROM table WHERE field = 'value'", but it starts becoming very helpful when you're doing long, complex INNER JOINS and other complicated SQL statements.

<?php

// control panel for multiple websies

$website = define it up here;
$item = define it up here;

function add_page($website_id, $item, $mysql)
{
    $next_page_count = increment_and_return_next_page_count($website_id, $mysql);

    create_new_page($item, $next_page_count, $mysql);
}

function increment_and_return_next_page_count($website_id, $mysql)
{
    $mysql['stmt'] = $mysql['conn']->prepare("
        SELECT website_pagecount
        FROM websites
        WHERE website_domain = ?
    ");
    $mysql['stmt']->bind_param(
        "s",
        $website_id
    );
    $result = $mysql['stmt']->execute();
    $result = $mysql['stmt']->get_result();
    $pagecount = $result->fetch_assoc();
    $mysql['stmt']->close();

    $pagecount++;

    $mysql['stmt'] = $mysql['conn']->prepare("
        UPDATE websites
        SET website_pagecount=?
        WHERE website_domain=?
    ");
    $mysql['stmt']->bind_param(
        "is",
        $pagecount,
        $website_id
    );
    $result = $mysql['stmt']->execute();
    $mysql['stmt']->close();

    return($pagecount);
}

function create_new_page($item, $next_page_count, $mysql)
{
    $mysql['stmt'] = $mysql['conn']->prepare("
        INSERT INTO pages (page_id,page_parent, page_title, page_description,page_keywords, website_id, page_layout)
        VALUES (?,?,?,?,?,?,?)
    ");
    $mysql['stmt']->bind_param(
        "issssss",
        $next_page_count,
        $item['parentpage'],
        $item['pagetitle'],
        $item['pagedescription'],
        $item['pagekeywords'],
        $website['website_id'],
        $item['pagelayout']
    );
    $result = $mysql['stmt']->execute();
    $mysql['stmt']->close();
}

add_page($website['website_id'], $item, $mysql);

?>

Other than those tips, I will defer to more experienced PHP coders. I hope my ideas help you out a little bit though. Good luck!

P.S. As for your original idea of combining the statements: I don't think they can be combined because they are doing different things. One is SELECT, another is UPDATE, and the third is INSERT. If they were all doing the same thing, then you could combine them using various types of JOINS probably. But since they are performing different actions I don't think that is an option.