how to call up a record ??

[gnetuk]

Hi all.

 

Im trying to get a result on one of my pages where it says if a members is banned 1 or not 0,

 

I cannt seem to get it to work. What i need is some code that says.

 

get banned FROM usersgnet WHERE name = POST'name'

 

Then print the banned i,.e  1 = YES 0 = NO

 

Its just so i can tell if i have banned them or not.

 

Thanks

gnetuk

[Psycho]

Rule #12:

All request for code to be written for you should be posted under the freelance section. No exceptions.

 

This forum is for people wanting help with code they have written. Please show what you have tried and state what problems you have encountered.

[gnetuk]

 

This is to get the results

 

 

<?

$bancheck = mysql_query("SELECT banned FROM usersgnet WHERE name = '$userword'") or die(mysql_error());

?>

 

this is to print it

 

echo $bancheck

 

this is what it prints

 

echo Resource id #6;

 

Sorry if i have posted to the wrong place.

[Zane]

it prints Resource id #x, because that's what the $bannedcheck variable is... a resource.  You have to combine this resource with mysql_fetch_assoc, mysql_fetch_array, or mysql_fetch_row

[Psycho]

it prints Resource id #x, because that's what the $bannedcheck variable is... a resource.  You have to combine this resource with mysql_fetch_assoc, mysql_fetch_array, or mysql_fetch_row

OR mysql_result() which is my preference when getting a single value from a single record. Also, I would advise not building your queries inside the mysql_query() function - it's easier to debug query errors when you can echo the query to the page.

 

$query = "SELECT banned FROM usersgnet WHERE name = '$userword'";
$result = mysql_query($query) or die(mysql_error());
if(!mysql_num_rows($result))
{
    echo "No match found.";
}
else
{
    $banned_status = (mysql_result($result, 0)=='1') ? 'BANNED' : 'NOT BANNED';
    echo $banned_status;
}

[gnetuk]

nice one, u peeps have gave me somtink to work with.

 

i get "No match found." even though the user is banned.

 

the field is called banned and the values are 1 or 0 , 0 beeing not and 1 yes.

 

the table is called usersgnet

 

why would it say no match even if they are set to 1 i only want to print 1 or 0 as i know what it will mean, puting a name is nicer but i think im goina have to get a beer and a re think .

 

any ideas?

 

gnetuk

[Zane]

Is your banned field set to BOOL?

[gnetuk]

Is your banned field set to BOOL?

 

 

i dont under stand.  BOOL?

 

i can reed php but cant write it

 

if you mean what my field is it is.

 

# Column Type Collation Attributes Null Default Extra Action

  3  banned int(11)  No None 

 

[Zane]

Even though having a datatype of int(11) will probably work, you only need to store a 1 or a 0.  MySQL offers a datatype called BOOL, which is essentially just int(1)

[Psycho]

The "No match found" has nothing to do with the "banned" field. That response means there is no record in the table that matches the WHERE clause

WHERE name = '$userword'

 

Echo the query to the page and verify that the value of $userword is what you expect. If so, check the actual database to verify there is a matching record (which there isn't - thus the error).

[gnetuk]

i changed the query to

 

WHERE name = '$user'";

 

this seems to pull a result but the result is that NOT BANNED for all but i have one that is banned in my table.

 

$banned_status = (mysql_result($result, 0)=='1') ? 'BANNED' : 'NOT BANNED';    is the result code

 

if i change the 0 1 around the result is opposit BANNED for all records.

[gnetuk]

i changed the query to

 

WHERE name = '$user'";

 

this seems to pull a result but the result is that NOT BANNED for all but i have one that is banned in my table.

 

$banned_status = (mysql_result($result, 0)=='1') ? 'BANNED' : 'NOT BANNED';    is the result code

 

if i change the 0 1 around the result is opposit BANNED for all records.

 

i just figured out it works if i set the WHERE name = 'the actual name'  but the page im pulling the results from is on a different table in my database, i goto tell the WHERE bit to look up the names from one table and match it to the other. oh dear..........

[litebearer]

show us the structure of both tables

[gnetuk]

This is the b_users table, the forum one

 

-- Table structure for table `b_users`

--

 

CREATE TABLE `b_users` (

  `userID` bigint(21) NOT NULL auto_increment,

  `username` varchar(60) NOT NULL default '',

  `password` varchar(255) NOT NULL default '',

  `status` int(20) NOT NULL default '0',

  `posts` bigint(20) NOT NULL default '0',

  `email` varchar(255) NOT NULL default '',

  `validated` int(11) NOT NULL default '0',

  `keynode` bigint(21) NOT NULL default '0',

  `sig` tinytext NOT NULL,

  `banned` varchar(255) NOT NULL default 'no',

  `rank` varchar(255) NOT NULL default '0',

  `usepm` int(11) NOT NULL default '1',

  `AIM` varchar(50) NOT NULL default '',

  `ICQ` varchar(50) NOT NULL default '',

  `location` varchar(255) NOT NULL default '',

  `showprofile` smallint(6) NOT NULL default '1',

  `lastposttime` bigint(20) NOT NULL default '0',

  `tsgone` bigint(20) NOT NULL default '0',

  `oldtime` bigint(20) NOT NULL default '0',

  `avatar` varchar(255) NOT NULL default '',

  `photo` varchar(255) NOT NULL default '',

  `rating` bigint(255) NOT NULL default '0',

  `totalvotes` bigint(20) NOT NULL default '0',

  `votedfor` longtext NOT NULL,

  `rps` int(11) NOT NULL default '1',

  `rpsscore` bigint(20) NOT NULL default '0',

  `lasttime` bigint(20) NOT NULL default '0',

  `templateclass` bigint(20) NOT NULL default '1',

  PRIMARY KEY  (`userID`)

) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=102 ;

 

 

 

 

This is the usersgnet the MAIN login table

 

--

-- Table structure for table `usersgnet`

--

 

CREATE TABLE `usersgnet` (

  `id` int(20) NOT NULL auto_increment,

  `name` varchar(200) character set latin1 collate latin1_general_ci NOT NULL default '',

  `banned` int(1) NOT NULL,

  `users_last_click_at` datetime NOT NULL,

  `user_pwd` varchar(200) character set latin1 collate latin1_general_ci NOT NULL default '',

  `user_email` varchar(200) character set latin1 collate latin1_general_ci NOT NULL default '',

  `activation_code` int(10) NOT NULL default '0',

  `joined` date NOT NULL default '0000-00-00',

  `country` varchar(100) character set latin1 collate latin1_general_ci NOT NULL default '',

  `user_activated` int(1) NOT NULL default '0',

  PRIMARY KEY  (`id`)

) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=102 ;

 

Notice there are two banned fields in both of the tables this is for banning on the forum and the main login. (The forum part works)

 

This is the code

 

        $userword=$_POST['userword'];

        $getusers="SELECT * from b_users where username like '%$userword%' and username!='Guest' order by username ASC limit $start, 30";

        $getusers2=mysql_query($getusers) or die("Could not get users");

        print "<table class='maintable'>";

        print "<tr class='headline'><td>test</td><td>Username</td><td>Email</td><td>Title</td><td>status</td><td>BAN/g-netUK?</td><td>UNBAN/g-netUK?</td><td>Banned?/POST</td><td>BAN/POST</td><td>UNBAN/POST</td><td>Delete</td></tr>";

     

$query = "SELECT banned FROM usersgnet WHERE name = '$name'";

$result = mysql_query($query) or die(mysql_error());

if(!mysql_num_rows($result)){    echo "No match found.";

}else{    $banned_status = (mysql_result($result, 0)=='1') ? 'BANNED' : 'NOT BANNED';

    $banned_status;}

 

  while($getusers3=mysql_fetch_array($getusers2))

        {

          $status=getstatus($getusers3[status]);

          print "<tr class='forumrow'><td>$banned_status</td><td>$getusers3[username]</td><td>$getusers3</td><td>$getusers3[rank]</td><td>$status</td><td><A href='gnetban.php?name=$getusers3[username]'>BAN/g-netUK</a></td><td><A href='gnetban0.php?name=$getusers3[username]'>UNBAN</a></td><td>$getusers3[banned]</td><td><A href='edituser.php?name=$getusers3[username]'>BAN/POSTING</a></td><td><A href='edituser0.php?name=$getusers3[username]'>un-BAN/POSTING</a></td><td><A href='deleteuser.php?name=$getusers3[username]'>Delete</td></tr>";

        }

 

the problem is selecting the name from the b_users and matching it with the usergnet, if i change the name part to a banned name it says BANNED on all names in the print report of all members.

 

Hope this makes sence.

 

gnetuk

[Drummin]

And what happens when you replace the variable $name with $userword like you have in your first query?  I think using something more definitive like userID in both tables would help match records.

<?php
$userword=$_POST['userword'];
$userword=mysql_real_escape_string(trim($userword));
	$getusers="SELECT * from b_users where username like '%$userword%' and username!='Guest' order by username ASC limit $start, 30";
         $getusers2=mysql_query($getusers) or die("Could not get users");
         print "<table class='maintable'>";
         print "<tr class='headline'><td>test</td><td>Username</td><td>Email</td><td>Title</td><td>status</td><td>BAN/g-netUK?</td><td>UNBAN/g-netUK?</td><td>Banned?/POST</td><td>BAN/POST</td><td>UNBAN/POST</td><td>Delete</td></tr>";

$query = "SELECT banned FROM usersgnet WHERE name = '$userword'";
	$result = mysql_query($query) or die(mysql_error());
if(!mysql_num_rows($result)){    echo "No match found.";
}else{    $banned_status = (mysql_result($result, 0)=='1') ? 'BANNED' : 'NOT BANNED';
    $banned_status;}

  while($getusers3=mysql_fetch_array($getusers2))
         {
           $status=getstatus($getusers3[status]);
           print "<tr class='forumrow'><td>$banned_status</td><td>$getusers3[username]</td><td>$getusers3[email]</td><td>$getusers3[rank]</td><td>$status</td><td><A href='gnetban.php?name=$getusers3[username]'>BAN/g-netUK</a></td><td><A href='gnetban0.php?name=$getusers3[username]'>UNBAN</a></td><td>$getusers3[banned]</td><td><A href='edituser.php?name=$getusers3[username]'>BAN/POSTING</a></td><td><A href='edituser0.php?name=$getusers3[username]'>un-BAN/POSTING</a></td><td><A href='deleteuser.php?name=$getusers3[username]'>Delete</td></tr>";
         }
?>

[Psycho]

Your database structure is flawed. You should not be storing the same data for records in different tables. I see some of the exact same data in the two tables. I think you may need to do some research on how to properly create a database (see database normalization). This is not a trivial task, but one you need to understand.

 

I am not going to even attempt to provide code to work with what you have as it needs a complete overhaul.

[gnetuk]

Yes your right, I am a compete noob to this, my problem is my WP BLOG, FORUM, MAIN SITE need the same data in there tables so its consistant throught the site.

 

Rarther than having to loggin to the seprate features if you get me drift.

 

I found this peice of code seems to get the desired result

 

$SQL = "SELECT banned FROM usersgnet";

$result = mysql_query($SQL);

 

while ($banned = mysql_fetch_assoc($result)) {

print $banned['banned'] . "<BR>";

}

 

Im not asking for a big job but ill take all your advise on board.

 

Thanks eveyone

 

gnetuk

[gnetuk]

And what happens when you replace the variable $name with $userword like you have in your first query?  I think using something more definitive like userID in both tables would help match records.

<?php
$userword=$_POST['userword'];
$userword=mysql_real_escape_string(trim($userword));
	$getusers="SELECT * from b_users where username like '%$userword%' and username!='Guest' order by username ASC limit $start, 30";
         $getusers2=mysql_query($getusers) or die("Could not get users");
         print "<table class='maintable'>";
         print "<tr class='headline'><td>test</td><td>Username</td><td>Email</td><td>Title</td><td>status</td><td>BAN/g-netUK?</td><td>UNBAN/g-netUK?</td><td>Banned?/POST</td><td>BAN/POST</td><td>UNBAN/POST</td><td>Delete</td></tr>";

$query = "SELECT banned FROM usersgnet WHERE name = '$userword'";
	$result = mysql_query($query) or die(mysql_error());
if(!mysql_num_rows($result)){    echo "No match found.";
}else{    $banned_status = (mysql_result($result, 0)=='1') ? 'BANNED' : 'NOT BANNED';
    $banned_status;}

  while($getusers3=mysql_fetch_array($getusers2))
         {
           $status=getstatus($getusers3[status]);
           print "<tr class='forumrow'><td>$banned_status</td><td>$getusers3[username]</td><td>$getusers3[email]</td><td>$getusers3[rank]</td><td>$status</td><td><A href='gnetban.php?name=$getusers3[username]'>BAN/g-netUK</a></td><td><A href='gnetban0.php?name=$getusers3[username]'>UNBAN</a></td><td>$getusers3[banned]</td><td><A href='edituser.php?name=$getusers3[username]'>BAN/POSTING</a></td><td><A href='edituser0.php?name=$getusers3[username]'>un-BAN/POSTING</a></td><td><A href='deleteuser.php?name=$getusers3[username]'>Delete</td></tr>";
         }
?>

 

Thanks for trying i get the sql error No match found.

[Psycho]

Yes your right, I am a compete noob to this, my problem is my WP BLOG, FORUM, MAIN SITE need the same data in there tables so its consistant throught the site.

 

Rarther than having to loggin to the seprate features if you get me drift.

 

Not to be rude, but no. If you already have a table that has user info and you want to extend the data you want to collect for users you should either expand the existing table OR, if you can't do that, you create another table with ONLY the new information. If you do create a new table you can associate the data between the two tables using a JOIN in your query. If you want CONSISTENT data the absolute worst thing to do would be to copy data into two tables.

 

I really can't explain all the ins and outs about database design and how you do JOINs but there are tons of tutorials out there.

[gnetuk]

 

 

Not to be rude, but no. If you already have a table that has user info and you want to extend the data you want to collect for users you should either expand the existing table OR, if you can't do that, you create another table with ONLY the new information. If you do create a new table you can associate the data between the two tables using a JOIN in your query. If you want CONSISTENT data the absolute worst thing to do would be to copy data into two tables.

 

I really can't explain all the ins and outs about database design and how you do JOINs but there are tons of tutorials out there.

 

Yes this is what i have done as the tables need to same data i am doing the worst thing by adding regesterd users into 3 tables with the same pass etc.

 

Dont understand why this is bad practice but if you can point me in the right direction it would help.

 

You can mark this as solved now nice one all XD

[Drummin]

Just add a field `banned` to your primary user table and update the field as needed.

[Psycho]

Dont understand why this is bad practice but if you can point me in the right direction it would help.

 

You can mark this as solved now nice one all XD

 

Well, think about it for a moment. Why would it be a good practice to copy data for the same records into multiple tables and then have to try and keep them in sync. As I stated above there are plenty of tutorials out there about database design that would do a much better job explaining than I could in a forum post. But, I'll try and provide a generalized example.

 

So, let's say you are using a Word Press framework and you want to add some functionality that requires you to capture more data about users. You can wither add those fields to the existing table or you can create a new table with ONLY the new fields as well as a foreign key for the record in the original table.

 

So, I would suggest you try and add the new columns to the existing table to see if it breaks any functionality in WP. If built correctly it shouldn't create any problems. But, if you do see any problems then simply delete those fields and create a new table with a field for a reference to the ID from the original table and ONLY the new fields.

 

Ok, so let's say you need to create the new table (we'll call is "user2") and you want to capture data for "banned", "rank" and "sig". The table should have those three fields and a field called "user_id" which will hold the "id" value from the existing "usersgnet". Then, when you let the user enter their information for "banned", "rank" and "sig" you would simply add a record to the new table with those value and the user's id.

 

All the WP functionality will still use the original table as it currently does. Now, you can use both that WP table and your new table for the new functionality you want to build. Let's say you need some data from the original table and the new table. You simply need to use a JOIN.

 

Example

SELECT name, user_email, banned, rank
FROM usersgnet
JOIN user2 ON usersgnet.id = user2.user_id

 

The above query would get the data specified for all users from the two tables. That is just a rough example.

 

Plus, as I was stating before you should also not be doing these queries using the "name" of the user. You should be using the ID. The only time you would normally be using a "name" as part of the WHERE clause is if you were doing a search.

 

 

You can mark this as solved now nice one all XD

I could, but mark it solved yourself. I'm not your bitch.

[gnetuk]

Hahahaha nice one still lol ill close this post then.

 

XD