PHP & SQL basic help please ?

[fenris_w0lf]

well...lol...I'm way to n00b for this (

I've been messing around trying to get what I wanted;

A php article fetching 4 fields from smf_members ordered by some field I added

but...alas...I get all sorts of errors (

Could someone pretty please help with posting what is mandatory for such a php article ??

[gerrymo]

I take it you know not to include the php tags? A recent topics SSI call woul look like this in ssi-examples:

Code Select Expand


<?php ssi_recentTopics(); ?>


But to work in a block/article it should look:

Code Select Expand


ssi_recentTopics();


In other words, strip out:

Code Select Expand


<?php
and
?>


[Rasyr]

what 4 fields are you wanting, and how do you want them displayed?

I'll see if I can come up with something for you...

[fenris_w0lf]

I've edited the table (eek!) and added a few fields, so lets just call them

memberName, field1, field2, field3

The select itself will prolly be something like:

select memberName, field1, field2, field3 from smf_members where (field1 is not null or field2 is not null or field3 is not null) order by memberName desc

HUGE thanks for helping !!!

[Rasyr]

Okay, I am at work at the moment, but I will see what I can come up with this evening....

[Rasyr]

Ok, now you have to remember that I am only a noob when it comes to working with PHP (never touched it before beginning of December), and am only vaguely familiar with SQL, so this is quite likely not as optimized as it could be.

Also, to make sure that I had this working good, I used real field names from the members table. You can replace them with the actual field names you are using. You will have to change the names in a number of locations.

Here is the code block:

Code Select Expand


global $context, $db_prefix;
$request = db_query("SELECT memberName, realName, usertitle, emailAddress, AIM  FROM {$db_prefix}members ORDER BY memberName DESC", __FILE__, __LINE__);
if (mysql_num_rows($request) > 0)
{
   while($row = mysql_fetch_assoc($request)){
$context['myCode'][] = array(
'name' => $row['memberName'],
'realName' => $row['realName'],
'title' => $row['usertitle'],
'email' => $row['emailAddress'],
'aim' => $row['AIM'],
);
  }
  mysql_free_result($request);
}

echo '<table border="1" cellspacing="0" cellpadding="5" align="center">';
echo '<tr bgcolor="#b0c4de"><td><b>Member Name</b></td><td><b>Real Name</b></td><td><b>User Title</b></td><td><b>Email</b></td><td><b>AIM</b></td></tr>';
foreach ($context['myCode'] as $myMember)
{
if ($myMember['realName'] == '' || $myMember['title'] == '' || $myMember['email'] == '' || $myMember['aim'] == '')
$test = false;
else
$test = true;

if($test)
{
echo'<tr><td>'. $myMember['name'] .'</td><td>'. $myMember['realName'] .'</td><td>'. $myMember['title'] .'</td><td>'. $myMember['email'] .'</td><td>'. $myMember['aim'] .'</td></tr>';
}
}
echo '</table>';


You will notice that we started off with the global line. That is there to bring in the global variables that we will be using.

Next is the line that hold the SQL statement. I have no idea how to make it skip those table rows that are blank, so I didn't worry about it there.

After that, I dumped the results into an array.

Most of the SQL call code is cribbed from another function in the forum software, so I didn't reall mess with it.

Once I had my array, I then cycled through ti and formatted my results into a small table. It is here that I checkd for the null field, setting s temp variable to either true or false, and if true, I then output the row into my table.

The following two screenshots show the results. The first is where it is printing all of my results, and the second is the code fully working, stripping out those that I do not want.




[attachment deleted by admin]

[attachment deleted by admin]

[IchBin]

That is great work Rasyr. PHP since december? wow.... I wish I was half as fast as you at this....

One question though, can you do this for a member group too?

[Rasyr]

I'll give it a try either in the morning on tomorrow evening - am too tired this evening to give it a go....

[fenris_w0lf]

IchBinâââ,¬Å¾Ã,¢:

Yes you can do this for ANY of the tables in the smf database that you have select-right to.

Rasyr:

Awesome !!!! HUGE thanks !!

First, if you want to optimize your code a bit instead of:

Code Select Expand


$request = db_query("SELECT memberName, realName, usertitle, emailAddress, AIM  FROM {$db_prefix}members ORDER BY memberName DESC", __FILE__, __LINE__);


try this:

Code Select Expand


$request = db_query("SELECT memberName, realName, usertitle, emailAddress, AIM  FROM {$db_prefix}members where realName != "" and usertitle != "" and emailAddress != "" and AIM != "" ORDER BY memberName DESC", __FILE__, __LINE__);


(note: I havent checked that code on my end so be sure to double-check the result)

This should filter out the ones without any rows that have no values in the columns in question, I get a 0 rows result on this sql because none of my members have any values in the "usertitle" field. Notice that you do not need to test the field MemberName since *all* rows have values there. If you know for a fact that this is true for other of your fileds, then you can also remove those from the where statement. The optimization here is that you only fetch the rows that you're interested in so that the following php code gets less rows to work with, and you can remove the test-section of your php code.

Second; I cant get my article to show on the menu (

Upon making the article you can decide if its to show on the front-page or not. When I went back and selected that option, the table showed just fine on the front page !!?? (and very nicely so ) But for some reason it wont show up in my Main Menu...any ideas ?

I think this might have something to do with the "Edit Blocks" - "Main Menu item" - when I go to edit it; I see this:





(seems I had no Attachement option for the screenshot..... ?)

I had to add that "Chat" action to the action php when I was "fixing" the menu-item for the 3guk PJIRC chat.....does this mean I have to do this for EVERY php article !?

[Rasyr]

Click on that Menu Manager link. And add the page through there. Then in the Edit Blocks screen, make sure that the default block (that was installed when you installed TP - called Content, IIRC) is turned on. You will see the link in there.

If you deleted that block add in a new one of the type "menubox", and anything you already have in the menu manager will show up.

I am not sure where you got the main menu (as that replicates what is across the top). However, you can add these though the Menu Manager as well. Just select link and put in "index.php?action=xxx" in the third column. The first column being the name you want to show for the link.