Using PHP to display a SQL table for usage on C# program

OK, so really it seems that all you need is to make a small PHP page that returns the data from the SQL table in the format you specified (< username >:< email >:< usertype >-...). I trust you can do that, simply go something akin to:

function escape($str)
{
    $retn = str_replace("\\","\\\\",$str);
    $retn = str_replace("-","\\-",$retn);
    $retn = str_replace(":","\\:",$retn);
}
$result = SQL_QUERY_FUNCTION("SELECT * FROM UserDB");
$rowset = SQL_GET_ROWSET($result);
SQL_FREE_RESULT($result);
for ($i=0; $i<count($rowset); $i++)
{
    echo escape($rowset[$i]['username']);
    echo ":";
    echo escape($rowset[$i]['email']);
    echo ":";
    echo escape($rowset[$i]['usertype']);
    echo "-";
}

(I'm not sure how you're going about getting your SQL database, just replace the functions with their approximate equivalents)

Also, if you're wondering about why the escape function is neccessary, it is because it is crucial to site security. Not neccessarily in this case though, but if you extend the functionality you could leave a hole open: imagine an email with a - in it: Garry:[email protected]:registered-... becomes Garry:garry and [email protected]:registered when split!

Then, the C# loop might look something like

List<List<string>> users = new List<List<string>>();
List<string> user = new List<string>();
StringBuilder potato = new StringBuilder();
int i=0;
bool escaped=false;
while (i<userDB.Length)
{
    if (userDB[i] == '\\' && !escaped)
    {
        escaped = true;
    }
    else if (userDB[i] == ':' && !escaped)
    {
         user.Add(potato.ToString());
         potato.Clear();
    }
    else if (userDB[i] == '-' && !escaped)
    {
         user.Add(potato.ToString());
         potato.Clear();
         users.Add(user);
         user = new List<string>();
    }
    else
    {
        potato.Append(userDB[i]);
        escaped = false;
    }
}

You could perhaps encode the :s and -s as special unicode characters if you wish, and then simply use the string.Split() function for something a little easier, but this way works too and you dont need to worry about encodings.

HTH.

EDIT: So, what we're doing is packing the strings in a way which we can read them out again. The biggest issue here is just escaping the strings, and as someone else mentioned JSON may indeed be the way forward here (forgot about that...). Nonetheless, I'm here to further explain my answer. So the idea is that we're using : and - as tokens, we are using them to separate the data out into blocks which we can read. If we were encoding 4 hellos using this, it would look like this:

Hello:Hello:Hello:Hello

Let's say that maybe we wanted to encode these 4 lines

He:llo
Hell:o
Hello
Hello

So if we ignored escaping we'd get

He:llo:Hell:o:Hello:Hello

And when the program tries to seperate these out, we'd get

He
llo
Hell
o
Hello
Hello

Which is certainly NOT what we put in before. So we use escaping. You probably know that if you're putting a " inside a string, you have to put a \ before it. Why? Same reason: there is no way for the computer to distinguish between a " in the middle and a " that ends the string. So we "escape" the quote. Imagine every time the computer sees a \, it joins the \ and the next character. So it's now very easy to distinguish between the two quotes:

"Hello, My name is "Matt""

vs

"Hello, My name is \"Matt\""

See? Now with the understanding that a \ joins itself and the next character, the only way to put one slash in is to put two in:

Console.WriteLine("This is a slash in quotes: \"\\\"");

would output:

This is a slash in quotes "\"

So, with what we now know about escaping, we can program it in. First thing we do is escape all the escape characters (Which is a ). (We have to do this step first, I'll explain later)

$retn = str_replace("\\","\\\\",$str);

It replaces every \ with \ in the string $str and puts it in $retn. But remember: \ is the escape character for PHP too! So to tell php what to replace, we have to escape it as well! Very confusing huh.

Next, we escape the tokens within the string: This way the -s and :s won't be accidentally seen as tokens. We simply replace the -s with - and the :s with \:, easy:

$retn = str_replace("-","\\-",$retn);
$retn = str_replace(":","\\:",$retn);

What's harder is reading: To read we have to walk through the string one character at a time until we reach the end. So: every time we read a "\", we ignore any special meaning the next character might have. Otherwise, we split up the List. Let's take a look:

List<List<string>> users = new List<List<string>>();

A list of lists: A group of collections of 3 strings, the username, the email and the usertype.

List<string> user = new List<string>();

This is the current user that we're adding data to. We add each string we read to this list until we reach a -. When we do, we just add the user to users, and make a new user for the next lot of data.

StringBuilder potato = new StringBuilder();

I won't go into why we should use StringBuilders here, but the basics of it is that it improves program speed by a significant degree over

string bob = "";
bob += "a";
bob += "b";
bob += "c";

So we hit the main loop:

while (i<userDB.Length)
{

For each character in the downloaded string...

if (userDB[i] == '\\' && !escaped)
{
    escaped = true;
}

if it is a slash AND a slash DID NOT preceed it, tell the next character that there was a slash before it.

else if (userDB[i] == ':' && !escaped)
{
     user.Add(potato.ToString());
     potato.Clear();
}

if it is a colon AND a slash DID NOT preceed it, put the current contents of the loaded string into the user and clear it for more data.

else if (userDB[i] == '-' && !escaped)
{
     user.Add(potato.ToString());
     potato.Clear();
     users.Add(user);
     user = new List<string>();
}

if it is a - AND a slash DID NOT preceed it, put the current contents of the loaded string into the user and clear it for more data, AND add the user to the list of users AND make another user for the next lot of data.

else
{
    potato.Append(userDB[i]);
    escaped = false;
}

if the character was escaped or it was not a /, : or -, we add it to the currently loaded string and clear any escape flag (I.e. tell the next character that a slash did not preceed it.

Hope that clears things up. As I said though, JSON does all this for you anyway. But if you don't understand what lies beneath, you can't learn how to do it by yourself!

Edit 2

There are two main ways of storing strings in computers. The first way is where we write the length of the word, and then we write the word.

5 Hello

The second way is where the string is stored in memory and ended by a null(0). You understand that letters are represented by their Unicode in 1, 2 or 4 bytes depending on the encoding (UTF8, UTF16/Unicode, UTF32). In fact, the letter "A" is stored as number 65. B is number 66, and so on. The point is that we choose a terminator character, number 0. So in fact "ABC" is stored this way like this (decimal):

65 66 67 0

But what if we weren't writing a string in english. What if we needed to put a 0 mid-way inside that string:

1 2 3 4 0 1 2 3 4 0

When the computer reads the string, it thinks that it ends at the 0 in the middle rather than at the end. So what do we do now? In most cases, we choose option 1. We transmit the length, and then the string itself. You could do this, but it would not work well for reasons I won't go into.

So how do we tell the middle 0 apart from the final null 0? We could pick say 5 to end this string, and nothing would go wrong until we needed to put a 5 in the string too. So we do something clever. We pick an escape character: It escapes from the string reading loop and uses the next character as an instruction. Here's the basic idea:

Is this character the escape character? YES: Grab the next character. If the next character is "A": Put an escape character in the string (so there is a way of encoding escape characters) If the next character is "!": The string is finished! OTHERWISE: ERROR. NO: Go to the next character.

So for this type of code, say we pick the escape character B. Let's try to encode the string "ABCDEFG".

First, we can see that the computer will reach the B and try to use "C" as an instruction and hit an ERROR. So to encode the B, we stick an A after it. Usually, we choose the instruction character for encoding the escape character to be the escape character itself: it just looks neater that way, there's no special reason. Next thing we need to do is tell the computer that the string is finished by adding "B!" to the end. So our final string looks like this:

ABACDEFGB!

So how does this apply to your problem? You have email addresses and usernames that you wish to split apart with colons. If the username itself CONTAINS a colon, the program will assume that the end of the name has been reached.

Bob:Boberty:[email protected]:registered

The computer will automatically assume that "Boberty" is infact the email. You need to tell it how to distinct a colon in a name from a colon that ends the string. Escaping is the answer.

I suggest you to take a look at JSON. You can convert a multi-array to JSON with json_encode($array) and in c# you can use JavaScriptSerializer to decode it to a array.

What people are doing seems quite complicated. What you could do is use a loop with an array list. But first add the inputs to the array by getting the table size of the MySQL table,loop though that size and added all the inputs seperated by '-'. Then loop though them and seperate them in the arraylist. something like

for(int x = 0;x < ArrayList.Size();x++)
{
      String[] Details = x.Split(new char[] { '-' });
      Lists.items.add(Details[0].trim());
}

Should work fine if you get the details properly. i didnt it briefly with one input.