[RESOLVED] Can't get data from one table

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
LuiePL
Forum Commoner
Posts: 40
Joined: Fri Aug 04, 2006 11:38 pm

[RESOLVED] Can't get data from one table

Post by LuiePL »

I'm trying to get information from the "Users" table. I can connect to the "Login" table (which is in the same database) without a problem, so I don't think it's a connectivity issue.

Code: Select all

$dbh = mysql_connect ("localhost", "user", "password") or die ('Database Connection Error: ' . mysql_error());
		mysql_select_db ("database");
		$queryadmin = "SELECT * FROM Login WHERE name='$name' AND admin='-1'";
			$sqladmin = mysql_query($queryadmin);
			$countadmin = mysql_num_rows($sqladmin);

		if ($countadmin == 1) //Checks to see if you're an Administrator
			{
				$querymem = "SELECT * FROM Users"; //Gets all the records from the "User" table
				$sqlmem = mysql_query($querymem);
				
				echo "<select size='1' name='number'>";
				
				while ($row = mysql_fetch_array($sqlmem))
					{
						$option = $option."<option value=".$row['number'].">".$row['number']."</option>"; //Should go through every record getting the "number"
					}
				echo $option;
				echo "</select>";
			}
I'm using the same code on another page but refering to the "Login" table and it works without a problem.
Last edited by LuiePL on Tue Aug 08, 2006 5:22 pm, edited 1 time in total.
blackbeard
Forum Contributor
Posts: 123
Joined: Thu Aug 03, 2006 6:20 pm

Re: Can't get data from one table

Post by blackbeard »

LuiePL wrote:I'm trying to get information from the "Users" table. I can connect to the "Login" table (which is in the same database) without a problem, so I don't think it's a connectivity issue.

Code: Select all

$dbh = mysql_connect ("localhost", "user", "password") or die ('Database Connection Error: ' . mysql_error());
		mysql_select_db ("database");
		$queryadmin = "SELECT * FROM Login WHERE name='$name' AND admin='-1'";
			$sqladmin = mysql_query($queryadmin);
			$countadmin = mysql_num_rows($sqladmin);

		if ($countadmin == 1) //Checks to see if you're an Administrator
			{
				$querymem = "SELECT * FROM Users"; //Gets all the records from the "User" table
				$sqlmem = mysql_query($querymem);
				
				echo "<select size='1' name='number'>";
				
				while ($row = mysql_fetch_array($sqlmem))
					{
						$option = "<option value="{$row['number']}">{$row['number']}</option>"; //Should go through every record getting the "number"
                                               echo $option;
					}
				
				echo "</select>";
			}
I'm using the same code on another page but refering to the "Login" table and it works without a problem.
Try the above, I moved the echo $option to inside the while loop, and modified the $option var.

Of course, have you verified that $countadmin really does equal 1?
LuiePL
Forum Commoner
Posts: 40
Joined: Fri Aug 04, 2006 11:38 pm

Post by LuiePL »

I tried that out but with no luck. But I noticed something I didn't really pick up on before. There are 4 options, which is the same amount of records that are in the table. So I think it's connecting, but not displaying the values. Also with "echo $option;" inside the while it shows 10 "options", so I moved it back out.

Also, I've logged in as a non-admin, and its setup do only display that users ID, which it does, so the $countadmin is working as expected.
LuiePL
Forum Commoner
Posts: 40
Joined: Fri Aug 04, 2006 11:38 pm

Post by LuiePL »

Just for the heck of it, I threw the query from the other page, and:

Code: Select all

$option = $option."<option value=".$row['name'].">".$row['name']."</option>";
from it too, and it loads the rown with no problems. But I can't get it to read the rows from the "Users" table. I even threw up a couple echo statements and it's not showing and name as being selected from the dropdown box, even though it shows the other information I put in.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Your second query is inside a conditional. If the conditional evaluates to false, the second query never runs. This certainly has nothing to do with connectivity. Check your conditonal to make sure it is evaluating to what you expect it to.

Code: Select all

if ($countadmin == 1)
Is this true or false?
LuiePL
Forum Commoner
Posts: 40
Joined: Fri Aug 04, 2006 11:38 pm

Post by LuiePL »

By default it would be true because the drop down is only within that conditional. So if it isn't true, it defaults to it showing just that user for the name. You know what I mean? I've tried it with a regular user and an admin, and it works.

Code: Select all

if admin = yes
  {
     echo "User:":
     echo "<option box>";
  }
else
  {
     echo "User: ".$name;
  }
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Next thing I can think of is error checking...

Code: Select all

$result = mysql_query($sql) or die(mysql_error());
Try these types of statement in your query results scripts and see if anything comes from that.
LuiePL
Forum Commoner
Posts: 40
Joined: Fri Aug 04, 2006 11:38 pm

Post by LuiePL »

I put that on every "reults" line and have "error_reporting(E_ALL);" at the top, and it's not showing anything different.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Ok, so if I understand this correctly, your first query executes, but the second one does not. The second one is in a conditional that you said defaults to true, so the second query should always execute. Is this correct?
LuiePL
Forum Commoner
Posts: 40
Joined: Fri Aug 04, 2006 11:38 pm

Post by LuiePL »

That's correct. Also, it is the same query as on another page but referring to a different table within the same database.

Could it be a problem with the table name "Users"? is there something in mySQL that refers to its own "Users" table?

This is how the tables are setup for the fields I'm looking up. First the "Login" table, then the "Users" table:

Field Type Collation
name varchar(16) ascii_bin
Number varchar(16) ascii_bin
LuiePL
Forum Commoner
Posts: 40
Joined: Fri Aug 04, 2006 11:38 pm

Post by LuiePL »

Boy, do I have egg on my face... it was a simple change from 'number' to 'Number'.... :x :oops:
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Do do you like your eggs scrambled or sunny-side up? :wink:
LuiePL
Forum Commoner
Posts: 40
Joined: Fri Aug 04, 2006 11:38 pm

Post by LuiePL »

Umm... sunny side up please :lol:
Post Reply