{"id":397,"date":"2021-06-10T14:14:34","date_gmt":"2021-06-10T13:14:34","guid":{"rendered":"https:\/\/blogs.glowscotland.org.uk\/sh\/ahscomputinghtml\/?page_id=397"},"modified":"2021-06-10T15:50:51","modified_gmt":"2021-06-10T14:50:51","slug":"php-and-sql","status":"publish","type":"page","link":"https:\/\/blogs.glowscotland.org.uk\/sh\/ahscomputinghtml\/php\/php-and-sql\/","title":{"rendered":"PHP and SQL"},"content":{"rendered":"

\"\"\"\"<\/a>PHP is often to use form data to read from, or update, SQL databases.<\/p>\n

Database Connection<\/h1>\n

Remember the order of parameters – you connect to the server<\/strong> with your name<\/strong> and password.<\/strong> Then you select the database<\/strong> to be used.<\/p>\n

In this example, three possible responses are given to a connection failure – it would be unlikely to use all three.<\/p>\n

\r\n$servername = "localhost";\r\n$username = "root";\r\n$password = "";\r\n$dbname = "studentlist";\r\n\r\n$conn = mysqli_connect($servername, $username, $password, $dbname);\r\n\r\nif (!$conn) {\r\n    echo '<img src="data:image\/gif;base64,R0lGODlhAQABAIAAAAAAAP\/\/\/yH5BAEAAAAALAAAAAABAAEAAAIBRAA7" data-wp-preserve="%3Cscript%3Ealert(%22Error%20inserting%20data%22)%3B%3C%2Fscript%3E" data-mce-resize="false" data-mce-placeholder="1" class="mce-object" width="20" height="20" alt="&lt;script&gt;" title="&lt;script&gt;" \/>'; \/\/ pop-up message\r\n    <p>Error inserting data<\/p> die("Error adding data");   \/\/ error on page\r\n    die("Connection to database failed");                   \/\/ halt script\r\n}\r\n<\/pre>\n
At the end of the script, the database connection should be closed:<\/p>\n
\r\nmysqli_close($conn);\r\n<\/pre>\n
SQL Insert\/Update\/Delete<\/h1>\n
These SQL queries retrun a Boolean True\/False result that can be used to check for success:<\/p>\n
\r\n$sql = "INSERT INTO students VALUES ($forename, $surname);\r\n$result = mysqli_query($conn, $sql);\r\nif (!result) { \r\n    echo '<img src="data:image\/gif;base64,R0lGODlhAQABAIAAAAAAAP\/\/\/yH5BAEAAAAALAAAAAABAAEAAAIBRAA7" data-wp-preserve="%3Cscript%3Ealert(%22Error%20inserting%20data%22)%3B%3C%2Fscript%3E" data-mce-resize="false" data-mce-placeholder="1" class="mce-object" width="20" height="20" alt="&lt;script&gt;" title="&lt;script&gt;" \/>';\r\n}\r\n<\/pre>\n
SQL Select<\/h1>\n
Select queries return will 0, 1 or more records:<\/p>\n
\r\n$sql = "SELECT forename, surname FROM students";\r\n$result = mysqli_query($conn, $sql);\r\n \r\n$studentsFound = mysqli_num_rows($result);\r\nif ($studentsFound >= 0) {\r\n    echo "<table>";\r\n    while($row = mysqli_fetch_array($result)) {\r\n        echo "<tr><td" . $row["forename"] . "<\/td><td>" . $row["surname"] . "<\/td><\/tr>";\r\n    echo "\/table>";\r\n} else {\r\n    echo "<p>No results found<\/p>"\r\n}\r\n\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"
PHP is often to use form data to read from, or update, SQL databases. Database Connection Remember the order of parameters – you connect to the server with your name and password. Then you select the database to be used. In this example, three possible responses are given to a connection failure – it would be unlikely to use all three. $servername = "localhost"; $username = "root"; $password = "";<\/p>\n
Read More<\/a><\/p>\n","protected":false},"author":7,"featured_media":0,"parent":343,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-397","page","type-page","status-publish","hentry"],"_links":{"self":[{"href":"https:\/\/blogs.glowscotland.org.uk\/sh\/ahscomputinghtml\/wp-json\/wp\/v2\/pages\/397","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blogs.glowscotland.org.uk\/sh\/ahscomputinghtml\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/blogs.glowscotland.org.uk\/sh\/ahscomputinghtml\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/blogs.glowscotland.org.uk\/sh\/ahscomputinghtml\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/blogs.glowscotland.org.uk\/sh\/ahscomputinghtml\/wp-json\/wp\/v2\/comments?post=397"}],"version-history":[{"count":9,"href":"https:\/\/blogs.glowscotland.org.uk\/sh\/ahscomputinghtml\/wp-json\/wp\/v2\/pages\/397\/revisions"}],"predecessor-version":[{"id":424,"href":"https:\/\/blogs.glowscotland.org.uk\/sh\/ahscomputinghtml\/wp-json\/wp\/v2\/pages\/397\/revisions\/424"}],"up":[{"embeddable":true,"href":"https:\/\/blogs.glowscotland.org.uk\/sh\/ahscomputinghtml\/wp-json\/wp\/v2\/pages\/343"}],"wp:attachment":[{"href":"https:\/\/blogs.glowscotland.org.uk\/sh\/ahscomputinghtml\/wp-json\/wp\/v2\/media?parent=397"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}