Forum Moderators: coopster
The code selects the ibf_members table. Within this table there is a "names" variable. These names variables are just the members names. The code prints the names in one long list in a table using an array. This works, the only thing that doesn't work is the simple if statement.
Here is what I want to accomplish in english code:
if "name" == Mike then print the line "its Mike!".
I can't figure out how to manipulate the mysql name variables. Here is the code:
<?php
$query = "SELECT * FROM `ibf_members` ";
$result = mysql_query($query) or die(mysql_error());
mysql_close();
$output = "
<html>
<body>
<table>
<thead>
<td>Name</td>
</thead>
";
while ($row = mysql_fetch_array($result))
{
$output .= "
<tr>
<td>".$row["name"]."</td>
</tr>
";
}
$output .= "
</table>
</body>
</html>
";
echo $output; //prints the list of names
//this doesn't work! How can I get this to work?
if ($output==Mike)
{
echo "its Mike!";
}
?>
So, the $output is the whole list of lets say, 150 names that joined my forum. How can I select a specific name in the list to manipulate? I want to manipulate the name "Mike". "Mike" is contained in the array $output I think.
You might want a different SELECT statement.
$query = "SELECT * FROM `ibf_members` WHERE name = 'Mike'";
But, that would only pull the one name, if it was there. Not sure if this is enough to meet your needs or not.
WBF
[edited by: chopin2256 at 6:25 am (utc) on Aug. 8, 2005]
Of course, the above snippet is VERY insecure. You want to filter all post data before letting it any where near your DB.
WBF
Of course, the above snippet is VERY insecure.
I am a newbie to php. Keep in mind that I am just modding invision board, and this board is pretty secure...so would I have to worry about any security for any custom code?
How can I make it secure?
Since you are new to coding PHP, this is a great time to familiarize yourself with the basic concepts of security of PHP. Don't do what I have done, write a bunch of scripts that all had to be modified in order to include or enhance security issues. It's easier to do it right the first time ;) And yes, your custom code is always a security related issue, no matter that you are modifying a script in some pre-packaged source.
There are few good threads about security in the PHP library. This one is a pretty good introduction to security and PHP [webmasterworld.com]. In particular, review the link in message #15. There is also a pretty good presentation (in pdf format) that breaks down the issues of filtering input and escaping output. That presentation is available here
[brainbulb.com...]
[edited by: grandpa at 6:47 am (utc) on Aug. 8, 2005]
[edited by: jatar_k at 7:39 am (utc) on Aug. 8, 2005]
[edit reason] linked it up [/edit]
There are a couple of excellent threads on these boards about security.
You will want to be sure that the post data is filtered to insure that it is what you expect. In this case it looks like you will want to be sure that all characters are alpha.
[us3.php.net ]
Before you take your mods live you really should take a bit to learn about security. Do a google search for "injection attacks"
What if they input their name as "George; DROP 'ibf_members';"
Now your query will read:
$query = "SELECT * FROM ibf_members WHERE name = 'George'; DROP ibf_members;"
OUCH!
WBF
I am still cleaning up code I wrote several years ago. Some of it is such spagetti that I just started over again. I have found security holes that a truck could have been driven through.
At the very least I would advise you to filter post and get data to insure it is what you expect, and then use escape all of your single quotes
If you expect numbers (e.g. a US postal code) don't accept letters.
Don't accept puncuation if you expect alphanumerics.
A little bit of learning now could save a lot of grief down the road.
WBF
Fair enough, but you did ask.
Oh no, I am glad you brought this to my attention, otherwise I would have never known! When I am ready I will bring this post back up again and refer to it for the security issues without a doubt. It's just that I don't know how I can implement security just yet, when I don't really know that much about php yet. But I will get there!
Again, thanks for bringing this to my attention.
$query = "SELECT * FROM `ibf_members` WHERE name = '$_POST[name]'";
I am still not exactly sure how to use this in an if statement. This is what I want to do but the syntax is not exactly correct:
if ($name==Mike)
{
echo "its Mike!";
}
What is the correct syntax for this simple piece of code?
Next, your variable $output is used to display the screen, so it's not really suitable for comparisons. For this example, I am simply testing the query results for 'Mike" and setting the variable $mike if the query is successful.
Then I use the variable $mike for the test condition in your if statement.
$mike = "";
while ($row = mysql_fetch_array($result))
{
$output .= "<tr><td>".$row["name"]."</td></tr>";
if ($row['name'] == 'Mike') { $mike = "Mike"; }
}
$output .= "</table></body></html>";
echo $output; //prints the list of names
//this should be working now.
if ($mike == "Mike")
{
echo "its Mike!";
}
There are some severe limitations to this implementation, but I think you'll see what I did to get the results. This example will generally produce poor results if your database contains more than one Mike.
For further clarification (or pontification), your original example did not work because the value of $output contains much more than the name. Let's assume you have two records in your table, John and Mike. The variable $output would contain this data:
<tr><td>John</td></tr><tr><td>Mike</td></tr></table></body></html>
Even if Mike were the only record in the table, your variable will contain all of this data:
<tr><td>Mike</td></tr></table></body></html>
And that is why this if statement would never be true.
if ($output == "Mike")
{
echo "its Mike!";
}
Now, everytime I want to manipulate a new database table, it seems I have to keep on opening up the database again. For example, I want to manipulate these two tables:
$query = "SELECT * FROM `ibf_members` ";
$query = "SELECT * FROM `ibf_attachments` ";
But I have to open up this code every time in order to get them to work:
$dbh=mysql_connect ("localhost", "user", "pass") or die ('I cannot connect to the database because: ' . mysql_error());
mysql_select_db ("dbname");
So the code would look like this:
---------------------------------------------------
$dbh=mysql_connect ("localhost", "user", "pass") or die ('I cannot connect to the database because: ' . mysql_error());
mysql_select_db ("dbname");
$query = "SELECT * FROM `ibf_members` ";
$result = mysql_query($query) or die(mysql_error());
mysql_close();
.
.
.
.
$dbh=mysql_connect ("localhost", "user", "pass") or die ('I cannot connect to the database because: ' . mysql_error());
mysql_select_db ("dbname");
$query = "SELECT * FROM `ibf_attachments` ";
$result = mysql_query($query) or die(mysql_error());
mysql_close();
.
.
.
.
and so on
-------------------------------------------------
How can I write this code so that I dont have to open up the database every time? Is there a way? Or is this how it is done?
Also, I want to compare variables from these two tables
ibf_members
ibf_attachments
Is this possible to do? Or can I only compare data within a table? For example, I want the attachment "Piano Solo" which is in the ibf_attachments table (that has a variable attach_member_id equal to 1) to be associated with member name "Mike" which is listed in the table ibf_members (and Mike is a variable that has a member_id equal to 1).
So I want "member_id" which equals 1 (Mike) to be associated with Piano Solo which has an "attach_member_id" equal to 1. Both these variables are in two different tables, and I need to associate these two. Is this possible?
use a connect function call once at the top of the script and then don't use mysql_close
for getting attachments
if you have Mike's id you can then use that in a select on the ibf_attachments table
select * from ibf_attachments where attach_member_id=1
or if you want to get both sets of data you could look into JOIN [dev.mysql.com]
attach_member_id = 1;
name = Mike
Now I want to output Mike, not "1"
How would I do this? Will I need an array?
List of names and ID
Mike id=1
jacob id=2
stefan inglis id=3
robtheman5824 id=4
aerlinndan id=5
Now I want to get these names in an array like this:
$name[0] = NULL;
$name[1] = Mike;
$name[2] = jacob;
$name[3] = stefan inglis;
$name[4] = robtheman5824;
$name[5] = aerlinndan;
attach_member_id will go in a loop from 0 to infinity (cap at some big number) with this code:
$i=0;
while ($i < 500,000)
{
$i++;
if($row['attach_member_id'] == $i)
{
echo $name[$i];
}
}
So basically the only hard part is how to get the names listed in the array? Like, how do I get $name[1] to be associated with Mike? 1 has to be Mike because attach_member_id is Mikes id. Unfornately I cant just play around with the names because I cannot predict what the name will be, and there is basically no way to code an array of "names"..only an array of "numbers". Therefore we need to work with ID's...but I want to convert id's to names for the output.
I hope this is clear.
Your explanation is pretty clear, and there any number of ways to produce the results. Quite often this will be handled in the table structure, by assigning a unique ID# to each name. The ID# can be in the same table or maintained in another table. Your database and table structures will make a difference in how efficiently you can process the data.
To add the list of existing names to an array is easy. But without your table structure I'm going to cheat and use my own table, which contains two fields, UserID and UserName. UserID is automatically generated when a new name is added to the table (Read autoincrement). Therefore, in order for this to work exactly as you describe, Mike must be the first name entered.
$sql = "CREATE TABLE names_list (
UserID int(11) NOT NULL auto_increment,
UserName varchar(55) default NULL
) TYPE=MyISAM;";
OK, so I built a table and added some names to it, Mike and John. Now I want those names in an array. I'm creating an array called $namearray and populating it in a while loop.
// build an array of name.
$sql = "SELECT * from names_list";
$result = mysql_query($sql);
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
$namearray[] = array($row['UserID'],$row['UserName'];
}
//When the loop has completed I'll display the names to the screen with this bit of code.
echo "<pre>";
print_r($namearray);
echo "</pre>";
And the results look like this.
Array
(
[0] => Array
(
[0] => 1
[1] => Mike
)
[1] => Array
(
[0] => 2
[1] => John
)
)
What do these alternating [0] and [1's] mean toward the bottom of your last post?
Here are my table structures...they are watered down to just the variables I want to manipulate which are "name" and "attach_member_id".
--------------------------------------------
CREATE TABLE `ibf_attachments` (
`attach_member_id` mediumint(8) NOT NULL default '0',
KEY `attach_mid_size` (`attach_member_id`,`attach_filesize`)
) TYPE=MyISAM AUTO_INCREMENT=421 ;
CREATE TABLE `ibf_members` (
`name` varchar(255) NOT NULL default '',
) TYPE=MyISAM;
--------------------------------------
I guess I can try adding tables like you said, although I would like to try to use the existing tables to keep this clean and simple so I can slowly understand.
Can you give us the complete table structure for ibf_members?
CREATE TABLE `ibf_members` (
`id` mediumint(8) NOT NULL default '0',
`name` varchar(255) NOT NULL default '',
`mgroup` smallint(3) NOT NULL default '0',
`legacy_password` varchar(32) NOT NULL default '',
`email` varchar(60) NOT NULL default '',
`joined` int(10) NOT NULL default '0',
`ip_address` varchar(16) NOT NULL default '',
`posts` mediumint(7) default '0',
`title` varchar(64) default NULL,
`allow_admin_mails` tinyint(1) default NULL,
`time_offset` varchar(10) default NULL,
`hide_email` varchar(8) default NULL,
`email_pm` tinyint(1) default NULL,
`email_full` tinyint(1) default NULL,
`skin` smallint(5) default NULL,
`warn_level` int(10) default NULL,
`warn_lastwarn` int(10) NOT NULL default '0',
`language` varchar(32) default NULL,
`last_post` int(10) default NULL,
`restrict_post` varchar(100) NOT NULL default '0',
`view_sigs` tinyint(1) default '1',
`view_img` tinyint(1) default '1',
`view_avs` tinyint(1) default '1',
`view_pop` tinyint(1) default '1',
`bday_day` int(2) default NULL,
`bday_month` int(2) default NULL,
`bday_year` int(4) default NULL,
`new_msg` tinyint(2) default '0',
`msg_total` smallint(5) default '0',
`show_popup` tinyint(1) default NULL,
`misc` varchar(128) default NULL,
`last_visit` int(10) default '0',
`last_activity` int(10) default '0',
`dst_in_use` tinyint(1) default '0',
`view_prefs` varchar(64) default '-1&-1',
`coppa_user` tinyint(1) default '0',
`mod_posts` varchar(100) NOT NULL default '0',
`auto_track` varchar(50) default '0',
`temp_ban` varchar(100) default '0',
`sub_end` int(10) NOT NULL default '0',
`login_anonymous` char(3) NOT NULL default '0&0',
`ignored_users` text NOT NULL,
`mgroup_others` varchar(255) NOT NULL default '',
`org_perm_id` varchar(255) NOT NULL default '',
`member_login_key` varchar(32) NOT NULL default '',
`subs_pkg_chosen` smallint(3) NOT NULL default '0',
`has_blog` tinyint(1) NOT NULL default '0',
`map` varchar(100) default NULL,
`map_location` varchar(15) default NULL,
PRIMARY KEY (`id`),
KEY `name` (`name`),
KEY `mgroup` (`mgroup`),
KEY `bday_day` (`bday_day`),
KEY `bday_month` (`bday_month`)
) TYPE=MyISAM;
$query = "SELECT ibf_members.name, ibf_attachments.attach_member_id FROM ibf_members, ibf_attachments";
$result = mysql_query($query) or die(mysql_error());
mysql_close();
$output = "<html><body><table><thead><td>Name</td></thead>";
while ($row = mysql_fetch_array($result))
$output .= "<tr><td>".$row["name"]."</td></tr>";
$output .= "</table></body></html>";
echo $output;
This works, as I can manipulate both variables now from the two tables...however, it gives me an infinite loop for some reason. It prints the names in alphabetical order and repeats over and over again...same thing for the ID's....why?
When I join the tables..it would be nice to see how the temporary data looks.
while ($row = mysql_fetch_array($result)) {
$output .= "<tr><td>".$row["name"]."</td></tr>";
}
RE: printing join table data: I could suggest dumping the results into an array and then printing the array.
My only problems now would be, how can I show 10, 20, 50, or X rows per page? For example...lets say I want 1 name, file, and anything else in that row per page. And I have 150 names. So thats 150 pages...which I have no idea how to do.
The last thing that I have to figure out is...how can I include links to the attachment files? Example:
I have attach_location (the url) and attach_file (the filename). How can I make attach_file be the anchortext, and attach_location be the url?
My only problems now would be, how can I show 10, 20, 50, or X rows per page? For example...lets say I want 1 name, file, and anything else in that row per page. And I have 150 names. So thats 150 pages...which I have no idea how to do.
MySQL has a LIMIT keyword you can use in your SELECT statement that will only return LIMIT matches. For example, "SELECT * FROM table LIMIT 11" would only return 11 rows from the table.
I have attach_location (the url) and attach_file (the filename). How can I make attach_file be the anchortext, and attach_location be the url?
print '<a href="'.$row['attach_location'].'">'.$row['attach_file'].'</a>';
