I'm not even sure how to ask the question properly, otherwise I probably would have found an answer. So, I will try to explain as best I can.
I'm working with a script called SLDB (I did not write these scripts), and as it stands, the scripts functions as advertised. The data that I store, I wish to call back into a website table (not what the scripts were designed to do).
Currently, the way SLDB is set up, it stores fields in this fashion:
ID Field Value
001 name name1
001 size size1
002 name name2
002 size size2
003 name name3
003 size size3
In the main script that passes the variables to the database.php script, it passes name and size as 'fields' and then each of their values as 'values'.
What I would like to display on the webpage is:
ID Name Size
001 name1 size1
002 name2 size2
003 name3 size3
In fact, I'd much rather rewrite the original script and database.php script to create and update the table in this fashion, but before I get into that, I would much rather find a way to accomplish my task without rewriting, if possible.
I am not proficient enough with mySQL to understand how to get the fields (Field, Value) with multiple variables to display separately in a single row. Currently, this is how it prints out:
http://wickednight.net/contests/photo-december/results.php
This is the simple php script I've written to display the table in html:
<?php
$con=mysqli_connect("localhost","***","***","***");
// Check connection
if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
$result = mysqli_query($con,"SELECT * FROM sldb_data");
echo "<table border='1' class='db-table' align='center'>
<tr>
<th>Name</th>
<th>Size</th>
</tr>";
while($row = mysqli_fetch_array($result))
{
echo "<tr>";
echo "<td>" . $row['field'] . "</td>";
echo "<td>" . $row['value'] . "</td>";
echo "</tr>";
}
echo "</table>";
mysqli_close($con);
?>
I realize, as written, this script is doing exactly as it's being asked to do. What I need to accomplish, however is obvious, I think. Any help is greatly appreciated.
This should work:
SELECT t1.uuid, t1.value AS name, t2.value AS size FROM sldb_data t1, sldb_data t2
WHERE t1.uuid = t2.uuid AND t1.field = "name" AND t2.field = "size"
Short explanation:
You can give a different name to a table in a query. This is nice when your tables have long or meaningless names. This is done in the FROM part. "sldb_data t1" gives the name "t1" to the table "sldb_data".
As we want to fetch two different rows from the same table we have to use the same table twice but we must give it two different names otherwise mysql is not knowing what exactly you want. You can think of it as if you had two identical tables.
Now we look for a row in t1 where field="name", in t2 where field="size" but only match those together with same uuid.
After the SELECT we just list the fields we want in our result. And again we give a name to the fields. But with fields you have to use the "as".