luke luke - 6 months ago 40
SQL Question

Fetching data from mysql using phpmyadmin for xcode

I have very minimal experience in php/mysql.

I am attempting to fetch data from mysql via myphpadmin and return it to xcode in a format it can understand.

<?php

// Create connection
$con=mysqli_connect("localhost","username","password","database");

// Check connection
if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

// This SQL statement selects ALL from the table 'Table1'
$sql = "SELECT * FROM Table1";

// Check if there are results
if ($result = mysqli_query($con, $sql))
{
// If so, then create a results array and a temporary one
// to hold the data
$resultArray = array();
$tempArray = array();

// Loop through each row in the result set
while($row = $result->fetch_object())
{
// Add each row into our results array
$tempArray = $row;
array_push($resultArray, $tempArray);
}

// Finally, encode the array to JSON and output the results
echo json_encode($resultArray);
}


// Close connections
mysqli_close($con);
?>


Now, while I do understand what's going on here to an extent, it only fetches data from one table. If I wanted to fetch data from multiple tables in the database, how could I edit the above code?

Answer

It depends what you are trying to achieve, you can join tables in a MySQL query, like:

SELECT o.*, c.companyName FROM orders o INNER JOIN customers c on o.customerId = c.id

This would return all of the order information and the customers company name from a related table.

If you want to return multiple different tables you may need to create multiple endpoints. maybe a customers.php, orders.php for example.

Personally myself, when creating a backend for a mobile project I will usually create a RESTful API using Symfony2, But this would take some time to learn.

This tutorial may help with understanding MySQL

Comments