TP-Docs
HTML5 Icon HTML5 Icon HTML5 Icon
TP on Social Media

Recent

Welcome to TinyPortal. Please login or sign up.

Members
  • Total Members: 3,972
  • Latest: Lineo
Stats
  • Total Posts: 196,105
  • Total Topics: 21,345
  • Online today: 1,072
  • Online ever: 8,223 (February 19, 2025, 04:35:35 AM)
Users Online
  • Users: 0
  • Guests: 147
  • Total: 147

How to convert sqldata to html using php

Started by fenris_w0lf, January 31, 2006, 11:29:30 PM

Previous topic - Next topic

0 Members and 1 Guest are viewing this topic.

fenris_w0lf

Sick of me yet ??  ;) ;D

I decided to try to code this (custom Memberlist) from scratch and use it as an php article instead, here's my code for the converting of sqldata to html (slightly edited one I got from Rasyr - thanks!! :))


echo'<tr><td>'. $myMember['memberName'] .'</td><td>'. $myMember['afk_picture'] .'</td><td>'. $myMember['location'] .'</td><td>'. $myMember['ICQ'] .'</td><td>'. $myMember['rank_aok'] .'</td><td>'. $myMember['rank_aoe3'] .'</td><td>'. $myMember['ID_GROUP'] .'</td><td>'. $myMember['dateRegistered'] .'</td></tr>';


The headings of the table is:
Member - Picture - Location - ICQ - AOK Rank - AOE3 Rank - Position - Registered

This code above will generate rows with something like:

MemberA - MemberA.jpg - USA.jpg - 1234567 - 9 - 0 - 33 - 1021075537
MemberB - MemberB.jpg - Norway.jpg - 7654321 - 10 - 3 - 33 - 1021075537

We can forget about the columns: Member, AOK Rank and AOE3 Rank, but for the rest I'd like the code to actually generate html that will:

* for the columns "Member" and "Location" show the images for "MemberA" and "USA.jpg" instead of the actual value saved in the database, so I need to incorporate the <a href= somehow..now I tried but with all the " and ' I figured I had to learn some groundrules.

* for the column "ICQ" show the "icq icon" and a <a href like the one in the standard memberlist that goes: http://web.icq.com/whitepages/online?icq=xxxxxxx&img=5

* for the column "Position" show the text-values (for us this is Villager, Scout, Hussar, Paladin, War Elephant etc) I guess here I'll need to join with a table - but I'm not quite sure which table these values are stored in

* for the column "Registered" I need to do some sort of convert to a more readable date-value.

...I'm starting to feel bad  for naggin this much ...  :-\  and indeed, if this is covered somewhere please guide me to it!! :)

Rasyr

You might want to also post this on the SMF forums as well. There are likely a lot of folks who know about coding on that site, and they might be able to help steer you in the right direction better than we can, especially since what you are wanting to do is basically a variation on a core SMF page, and they are likely more versed in the core SMF code as well (with a few exceptions, like Bloc).

Quote* for the columns "Member" and "Location" show the images for "MemberA" and "USA.jpg" instead of the actual value saved in the database, so I need to incorporate the <a href= somehow..now I tried but with all the " and ' I figured I had to learn some groundrules.

Actually, it is much like you have in the example above, only selecting WHERE to plug in the php variables.

For example, Here is a sample of how to do one for user images

echo ' <img src="my-path/folderName/'.$myMember['afk_picture'].'">';


Note the pattern. "echo" followed by a single quote, followed by html txt (including normal double quote placement, and at the end you have another single quote and a semi-colon.

In the middle there, where you want to place your php variable, you interrupt the string of text with a single quote, then a period (the php equivalent of a "+" in javascript), followed by variable name, followed by another period, and lastly another single quote, which begins the next non-php section. this part looks like the following: '.$myMember['afk_picture'].'

In the example I gave above, you will notice that there are what looks like 3 quote marks in a row after the image name variable. That is because the first character after the single quote is a double quote, which is used to close the src portion of the IMG tag.

Using the above methodology, you do not have to do anything special for the double quotes. However, if you need to include a single quote within the html portion of such text (like for use in a javascript function), then you will have to "escape" it. This means that you have to place a "\" (also called a backslash) in front of the single quote so that the php does not think that it is the end of a text string.

I would also recommend reading through this website -- http://www.w3schools.com/php/default.asp -- as it gives a lot of good information. There are at least two pages at that site devoted to the Date() function and that may help you with formatting the date the way you want.




fenris_w0lf

This is GREAT Rasyr!!!

The description you gave above will help more than just me I'm sure!! And that link to w3schools is bookmarked !!

To get this on its feet relatively fast I now only need help with the ICQ and Registered columns, posted @ SMF here:

http://www.simplemachines.org/community/index.php?topic=68827.0

Hopefully those two remaining columns will be easy for some of them :)

fenris_w0lf

ICQ now done,

still have to figure out the dateRegistered tho...problem is that it seems to be some sort of timestamp...

I did some reading on the getdate() function on that link you gave me (w3schools) but this:


$my_t=getdate(date($myMember['dateRegistered']));
$my_date=$my_t[yday] . " " . $my_t[month] . " " . $my_t[year] ;
echo $my_date;


But there's something weird going on..here's the 10 first rows:

129 May 2002
129 May 2002
133 May 2002
32 February 2003
128 May 2002
127 May 2002
127 May 2002
127 May 2002
129 May 2002
299 October 2003

What I'd *really* love is to utilize the profile-setup and format the dateRegistered according to the members preferences and in Profile.php I found something I probably could use, I've tried several variations but so far no luck (and also no replies over at SMF) :(

Code from Profile.php:

'registered' => empty($user_profile[$memID]['dateRegistered']) || $user_profile[$memID]['dateRegistered'] == '0000-00-00' ? $txt[470] : strftime('%Y-%m-%d', $user_profile[$memID]['dateRegistered'] + ($user_info['time_offset'] + $modSettings['time_offset']) * 3600),


But I'll settle with the first one if I could figure out why the extra numbers on day...

Rasyr

In Memberlist.php, in the Sources directory, there is the following code:


$context['members'][$member]['registered_date'] = strftime('%Y-%m-%d', $context['members'][$member]['registered_timestamp']);


If we strip this down, the function you want is going to look like the following:


strftime('%d %B %Y', $myMember['dateRegistered']);


That should return something like 1 February 2006

I used the information found on this page -- http://www.php.net/manual/en/function.strftime.php -- for aid in figuring the formating.



fenris_w0lf

Ohh...man THANKS!!!!!!!!!!11

:D

I've been trying what seems to be *everything* lol

I've bookmarked MySQL manual, PHP manual and the w3school page now.

Thanks!! :)

Its getting there now :D

fenris_w0lf

So..what do you think ????

http://www.federationofkings.com/index.php?page=23   (might change)

Next project is making the column-headings clickable to change sort-order.

But I think I'll let that wait a while...my head spins after makin this code lol

Thanks for the help!!!!!!

Rasyr

Looks pretty good, though you might want to adjust some of the column widths..

fenris_w0lf

oh,,but they seem to "auto adjust" ....atleast with my firefox 1.0.7  ??

This website is proudly hosted on Crocweb Cloud Website Hosting.