Page 1 of 1
[RESOLVED] Can't get data from one table
Posted: Mon Aug 07, 2006 5:21 pm
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.
Re: Can't get data from one table
Posted: Mon Aug 07, 2006 5:29 pm
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?
Posted: Mon Aug 07, 2006 5:44 pm
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.
Posted: Mon Aug 07, 2006 7:29 pm
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.
Posted: Mon Aug 07, 2006 7:42 pm
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.
Is this true or false?
Posted: Mon Aug 07, 2006 8:20 pm
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;
}
Posted: Tue Aug 08, 2006 12:48 pm
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.
Posted: Tue Aug 08, 2006 1:42 pm
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.
Posted: Tue Aug 08, 2006 3:53 pm
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?
Posted: Tue Aug 08, 2006 4:52 pm
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
Posted: Tue Aug 08, 2006 5:22 pm
by LuiePL
Boy, do I have egg on my face... it was a simple change from 'number' to 'Number'....

Posted: Tue Aug 08, 2006 5:23 pm
by RobertGonzalez
Do do you like your eggs scrambled or sunny-side up?

Posted: Tue Aug 08, 2006 5:24 pm
by LuiePL
Umm... sunny side up please
