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:
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.)
There's a query that then runs and grabs the new number.
Then another query runs and creates a row in a table called
pages
and sets the newpagecount
number in to thepage_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.
$mysql['stmt'] =
- do you refer to this object after executing it? Showing your whole function rather than a these few lines would be helpful.bind_param("is", $a = 1
- why bind a constant at all? \$\endgroup\$prepare("UPDATE websites SET website_pagecount=website_pagecount+1
- there's no need to make something fixed a variable - unless it is in fact variable, variables in a prepared statement are arbitrary - there's no rule that says allset x=?
have to be bound parameters. \$\endgroup\$