SLDB: Help Requested with Data Retrieval

[DexterBlake]

Hello,

I'm working with SLDB, and as it stands, the scripts function as advertised. My intention is not to bring the data back into SL, however. The data that I store, I wish to call back into a website table.

Currently, the way SLDB is set up, it stores fields in this fashion:

Key (the uuid key), Field, Value

Field contains: Name and Size

Value contains: (the name of the person) and (the agent size)

So, Field and Value contain multiple variables, with Key being the primary key and common denominator.

Where I run into a problem is that I want to be able to display information like this:

Name                                 Size
Dexterblake Resident     <1,1,1>
AnotherPerson Resident   <1,0,1>

I am not proficient enough with mySQL to understand how to get the fields (Field, Value) with multiple variables to display seperately in a single row. Currently, this is how it prints out:

http://wickednight.net/contests/photo-december/results.php

This is the simple php I've written:

<?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 appreciate any help you can give.

[Freya Mokusei]

My understanding of your post implies the mistake is actually in your PHP - your recordset isn't printing to HTML correctly.

Try replacing your PHP while with:-

$double = -1;
echo "<tr>";
while($row = mysqli_fetch_array($result))  {  if($double == 1) //End row every 2 records  {    $double = 0;
    echo "</tr>";    echo "<tr>";  }  $double = $double + 1;  echo "<td>" . $row['field'] . "</td>";  echo "<td>" . $row['value'] . "</td>";  }echo "</table>";

 Been a little while since I've played with PHP, but basically you need to get the while loop to fire twice between adding the <tr/><tr> block (which breaks the HTML table row).

There's probably a more efficient way to write this, I was hoping it would be self-explanatory.

Good luck!

[LepreKhaun]

---

DexterBlake wrote:

Hello,

I'm working with SLDB, and as it stands, the scripts function as advertised. My intention is not to bring the data back into SL, however. The data that I store, I wish to call back into a website table.

Currently, the way SLDB is set up, it stores fields in this fashion:

Key (the uuid key), Field, Value

Field contains: Name and Size

Value contains: (the name of the person) and (the agent size)

So, Field and Value contain multiple variables, with Key being the primary key and common denominator.

Where I run into a problem is that I want to be able to display information like this:

Name                                 Size

Dexterblake Resident     <1,1,1>

AnotherPerson Resident   <1,0,1>

I am not proficient enough with mySQL to understand how to get the fields (Field, Value) with multiple variables to display seperately in a single row. Currently, this is how it prints out:

http://wickednight.net/contests/photo-december/results.php

...

I appreciate any help you can give.

---

You've set up your database wrong, you shouldn't have explicitly named 'field' and 'value'. As it is, 'field' alternates between the strings "name" and "size" while 'value' is alternating between  (the name of the person) and (the agent size) and because you've doubled up values, you're getting doubled rows.

 

To straighten this out, you'll need to do a DB with the avatar key associated with the keys fields [Edit for correct term as it applies to a DB] 'name', which holds  (the name of the person) and 'size', which holds (the agent size), instead of having them as values. That way your code would look like:

while($row = mysqli_fetch_array($result))  {  echo "<tr>";  echo "<td>" . $row['name'] . "</td>";  echo "<td>" . $row['size'] . "</td>";  echo "</tr>";  }echo "</table>";

 As it is, with your doubled up values, you're going to have to limp by with:

while($row = mysqli_fetch_array($result))  {  echo "<tr>";  echo "<td>" . $row['value'] . "</td>";  $row = mysqli_fetch_array($result)  echo "<td>" . $row['value'] . "</td>";    echo "</tr>";  }echo "</table>";

 Until you figure all this out. I'd recommend you ditch this DB entirely and start over after studying a bit more. It really is going to be unworkable the way you've set it up.

 

And I do need to mention that this isn't the correct forum for this type of question, since it has nothing to do with LSL. There are many PHP/MySQL forums out there would be able to help far better.

 

[Freya Mokusei]

Your post makes sense to me at least. I would recommend the OP does as you say.

My post was working along the same lines as your second code block, Although I don't think I had a clear idea of what the OP was trying to do (why can't people post UML models along with their problems T_T).

[LepreKhaun]

I've actually read the documentation for LSDB and, well, it possibly isn't as clear on a lot of things as maybe it should be for someone who is not familiar with MySQL and PHP to begin with.

 

If the OP stays at it long enough to study what the terms 'field' and 'value' means, he'll have a good chuckle at his error.

[DexterBlake]

Thank you for your replies. I appreciate the help. Firstly, I'd like to say that you might have missed the point. I've made no errors. I didn't write the database,  didn't write the scripts, except for the basic php script to pull data into an html table.  
Also, this is from SLDB, from Aubretech, which has been discussed on these forums before, just not on this topic. 

Doubling up the field and value is how the script was written and not how I would have wanted it done. But, if I were to rewrite the LSL script that passes the values to http (and I did attempt that), then I would also have to rewrite the database.php script which parses those values and creates and updates the tables(which was written specifically for the way the LSL side was set up). Which is what Plan B was. I just figured I would ask (for once), rather than stumble into the answer. As far as I understand it, no support is offered for the SLDB script, so naturally since it is SL-DB.. my first stop was here.

I will try the advice about doubling the loop, Thank you. And meanwhile, LepreKhaun, I'll look forward to the chuckle I'm supposed to have when I figure out what you mean. That statement was confusing, since I do know what field and values mean... must be missing something.

You're right though, the documentation provided by the SLDB makers was pretty spotty. In fact, downloading the files from MP, gives you a link to the files with bad code. While attempting to fix this, I went to their documentation page, which had a different link to download the files, which had the 'fixed' code. But, as I said, they  don't offer support and pretty much tell you to figure out yourself, since it was free.

Edit to add: Btw, I did try this:

echo "<td>" . $row['name'] . "</td>";  echo "<td>" . $row['size'] . "</td>";

before I posted. But that gives me nothing, just a blank table. I thought that might be because the Select query needed to be written in another way.

Getting the loop to fire twice is probably the best way to handle this issue, although not the ideal thing to be doing. And I had already thought of that, but again.. not proficient enough to know what to do without either asking or more research. I would prefer to write things so that the table is 'id', 'uuid', 'name', 'size', 'time'. I'm unsure why the SLDB makers felt that setting up fields and variables was the easiest and most flexible solution.. but, we'll just chalk that up to my ignorance. Maybe down the road a light will turn on and I'll be all, "Oooh.. I get it now.. damn".

[LepreKhaun]

Good Luck with your free MP purchase, I'm certain it's worth every penny of that.

[DexterBlake]

Yes, it was definitely worth every penny of the purchase. About the same worth of the help I've received here. Sarcasm is beautiful. I should mention that neither solution offered worked. That is ok, I will keep working and post the solution for others who might have the same questions, or I will rewrite all the scripts to separate the fields and values.

[DexterBlake]

Here is the solution to the problem I originally posted.

As mentioned previously, SLDB doesn't store data in a way that is helpful if you wanted to expand on it for more than just spitting all the data back into SL. Due to that, my intention is to rewrite the scripts and offer them to other beginners to LSL and external databases. For now, however, in order to separate those values so that you can print them out in a table format, in a single row per uuid, you need to write your Select query in a way that basically renames your table data.

 

SELECT t1.uuid, t1.value AS name, t2.value AS size FROM sldb_data t1, sldb_data t2\n" . "WHERE t1.uuid = t2.uuid AND t1.field = \"name\" AND t2.field = \"size\"

You can give a different name to a table in a query. 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 doesn't exactly know what 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".

Now for the php script that calls the info into an html table, we use these renamed fields:

 

<html>	<head>	<title>Retrieve data from database </title>	<link href="sldb.css" rel="stylesheet" type="text/css">	</head>	<body><?php$con=mysqli_connect("localhost","****","****","****");// Check connectionif (mysqli_connect_errno())  {  echo "Failed to connect to MySQL: " . mysqli_connect_error();  }$result = mysqli_query($con,"SELECT t1.uuid, t1.value AS name, t2.value AS size FROM sldb_data t1, sldb_data t2\n" . "WHERE t1.uuid = t2.uuid AND t1.field = \"name\" AND t2.field = \"size\" ");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['name'] . "</td>";  echo "<td>" . $row['size'] . "</td>";  echo "</tr>";  }echo "</table>";mysqli_close($con);?>	</body>	</html>

You can compare that to the original post and you will see how we now are able to create a single row of all information grouped by UUID.  Although this isn't the recommended way of storing data, you can, technically add new fields and values to the LSL script, and just use the same principle of adding t3, t4, t5..etc, then grouping by the common UUID key.

[LepreKhaun]

I'm pleased you were able to figure out the answer to your problem. As I stated before, you would have gotten better responses in a PHP/MySQL forum.

 

If you ever do have a question specifically about LSL, this would be the appropriate place for it.