I'm currently still learning MySQL And prepared statements and i was wondering if this code i've written is the most efficient way.
The process...
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)
Theres 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 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'.. this is the code ive got.
$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();
Any suggestions or help would be greatly appreciated.