{"id":381,"date":"2019-08-21T16:31:32","date_gmt":"2019-08-21T15:31:32","guid":{"rendered":"https:\/\/blogs.gre.ac.uk\/cmssupport\/?page_id=381"},"modified":"2019-08-21T16:31:32","modified_gmt":"2019-08-21T15:31:32","slug":"connecting-to-the-mysql-server-with-php","status":"publish","type":"page","link":"https:\/\/blogs.gre.ac.uk\/cmssupport\/application-development\/programming\/php\/connecting-to-the-mysql-server-with-php\/","title":{"rendered":"Connecting to the MySQL server with PHP"},"content":{"rendered":"\n

In PHP (on the UNIX server or on the IIS server) you should use the following lines of code to connect to the MySQL server:<\/p>\n\n\n\n

$conn = @mysqli_connect(\"mysql.cms.gre.ac.uk\",\"userid\",\"password\",\"mdb_userid\");\n\nif (!$conn) {\n    \/* Use your preferred error logging method here *\/\n    error_log('Connection error: ' . mysqli_connect_errno());\n    echo \"Failed to connect to MySQL: \" . mysqli_connect_error();\n    exit();\n}\n\nif ($result = mysqli_query($conn, \"SELECT * FROM table\")) {\n\tprintf(\"Select returned %d rows.\\n\", mysqli_num_rows($result));\n\techo \"Result set order...\";\n\t$result->data_seek(0);\n\twhile ($row = $result->fetch_assoc()) {\n\t\techo $row['field1'].' '.$row['field2'];\n\t}\n\tmysqli_free_result($result);\n}\n\nmysqli_close($conn);<\/pre>\n\n\n\n
In all cases, the hostname is ‘mysql.cms.gre.ac.uk’, and your database is called ‘mdb_username’ (for example, the user ID aaa001 would have a database ‘mdb_aaa001’).<\/p>\n\n\n\n
For security reasons, you should ensure you use a different password for your SQL Server login than you do for other University systems.

For more information and a reference of all the commands available, go to the MySQL documentation page<\/a>.

The following example shows a PDO connection that will currently work on the web server ‘stuweb.cms.gre.ac.uk’ only:<\/p>\n\n\n\n
$db = new PDO('mysql:host=mysql.cms.gre.ac.uk;dbname=mdb_userid;charset=utf8','userid','mysql_password');\n$stmt = $db->query(\"SELECT * FROM table\");\n$row_count = $stmt->rowCount();\necho 'Number of Rows selected '. $row_count .'<p>';\necho \"While loop ....<p>\";\nwhile($row = $stmt->fetch(PDO::FETCH_ASSOC)) {\n    echo $row['col1'].' '.$row['col2'] .'<p>';\n}\necho \"Foreach loop ....<p>\";\nforeach($db->query('SELECT * FROM table') as $row) {\n    echo $row['col1'].' '.$row['col2'] .'<p>';\n}<\/pre>\n","protected":false},"excerpt":{"rendered":"
In PHP (on the UNIX server or on the IIS server) you should use the following lines of code to connect to the MySQL server: $conn = @mysqli_connect(“mysql.cms.gre.ac.uk”,”userid”,”password”,”mdb_userid”); if (!$conn) { \/* Use your preferred …<\/p>\n","protected":false},"author":53,"featured_media":0,"parent":122,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-381","page","type-page","status-publish","hentry"],"jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/blogs.gre.ac.uk\/cmssupport\/wp-json\/wp\/v2\/pages\/381","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blogs.gre.ac.uk\/cmssupport\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/blogs.gre.ac.uk\/cmssupport\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/blogs.gre.ac.uk\/cmssupport\/wp-json\/wp\/v2\/users\/53"}],"replies":[{"embeddable":true,"href":"https:\/\/blogs.gre.ac.uk\/cmssupport\/wp-json\/wp\/v2\/comments?post=381"}],"version-history":[{"count":0,"href":"https:\/\/blogs.gre.ac.uk\/cmssupport\/wp-json\/wp\/v2\/pages\/381\/revisions"}],"up":[{"embeddable":true,"href":"https:\/\/blogs.gre.ac.uk\/cmssupport\/wp-json\/wp\/v2\/pages\/122"}],"wp:attachment":[{"href":"https:\/\/blogs.gre.ac.uk\/cmssupport\/wp-json\/wp\/v2\/media?parent=381"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}