Page 1 of 2
Select * From Variable help
Posted: Fri Oct 24, 2008 11:13 am
by TheBrandon
Hello,
I'm trying to get this line of code to work. I have it working on other sites, but it won't execute in this script. Can anyone help me with the syntax?
Code: Select all
$sql = "SELECT * FROM $clientname";
Here is the full script (so far):
Code: Select all
<?require_once('/home/content/db/inc/global.php');
session_start();
if(!session_is_registered(myusername)){
header("location:index.php");
}
//get username
//$myusername=$_GET['id'];
//connect
db_connect();
//query
$sql="SELECT * FROM `clients` ORDER BY `client` ASC";
$result=mysql_query($sql) or die (mysql_error());
//stick our toe in the water
$totalpull = mysql_num_rows($result) or die (mysql_error());
$i=0;
while($totalpull > $i){
//Loop through client names
$array = mysql_fetch_array($result);
$clientname=($array["client"]);
echo $clientname;
echo "<br/>";
//select other db
//$fluid = "SELECT * FROM $tb2_surfers ORDER BY $tb2_id DESC LIMIT 1";
$sql = "SELECT * FROM $clientname";
$result=mysql_query($sql) or die (mysql_error());
$i++;
}
exit;
?>
I've been troubleshooting this myself for a while. I've tried all of these syntax versions and none of them work for me:
Code: Select all
$sql = "SELECT * FROM `$clientname` ";
$sql = "SELECT * FROM $clientname ";
$sql = "SELECT * FROM '$clientname' ";
$sql = 'SELECT * FROM $clientname ';
$sql = 'SELECT * FROM "$clientname" ';
$sql = 'SELECT * FROM '$clientname' ';
$sql = 'SELECT * FROM `$clientname` ';
$sql = "SELECT * FROM "$clientname" ";
$sql = "SELECT * FROM ."$clientname." ";
$sql = "SELECT * FROM $clientname";
$sql = ("SELECT 'status' FROM $clientname");
Any help would be greatly appreciated.
Re: Select * From Variable help
Posted: Fri Oct 24, 2008 12:00 pm
by oscardog
Code: Select all
$sql = "SELECT * FROM '$clientname' ";
I think
Re: Select * From Variable help
Posted: Fri Oct 24, 2008 12:06 pm
by requinix
" and ' mark strings.
` marks objects.
What's the error message, and have you made sure $clientname is a table?
Re: Select * From Variable help
Posted: Fri Oct 24, 2008 12:13 pm
by TheBrandon
oscardog wrote:Code: Select all
$sql = "SELECT * FROM '$clientname' ";
I think
$sql = "SELECT * FROM '$clientname' ";
Gives me this:
Code: Select all
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''Children_of_Bodom'' at line 1
This is my database structure for that table:
Code: Select all
CREATE TABLE `clients` (
`id` tinyint(255) NOT NULL auto_increment,
`client` tinytext character set utf8 collate utf8_unicode_ci NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;
--
-- Dumping data for table `clients`
--
INSERT INTO `clients` VALUES (3, 'Testing');
INSERT INTO `clients` VALUES (4, 'Children_of_Bodom');
And this is the Children of Bodom table:
Code: Select all
CREATE TABLE `Children_of_Bodom` (
`id` tinyint(255) NOT NULL auto_increment,
`status` mediumtext character set utf8 collate utf8_unicode_ci NOT NULL,
`priority` tinytext character set utf8 collate utf8_unicode_ci NOT NULL,
`user` tinytext character set utf8 collate utf8_unicode_ci NOT NULL,
`date` varchar(255) character set utf8 collate utf8_unicode_ci NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;
--
-- Dumping data for table `Children_of_Bodom`
--
INSERT INTO `Children_of_Bodom` VALUES (1, 'Aliquam at arcu. Vivamus nunc. Sed pharetra elementum felis. Sed eget magna sed diam vulputate blandit. In hac habitasse platea dictumst. Nam in ante. Aenean vel nibh at eros egestas placerat. Sed tincidunt dictum mauris. Cras sodales dictum urna. Ut massa urna, condimentum eget, porttitor sed, hendrerit in, magna. ', 'Medium', 'Shelley', '10.24.08');
Thanks for the explanation tasairis. Yes, $clientname is a table. Anything else that might help? I really appreciate the replies so far.
Re: Select * From Variable help
Posted: Fri Oct 24, 2008 12:23 pm
by requinix
Outside the loop
Code: Select all
$result=mysql_query($sql) or die (mysql_error());
and then again
inside the loop
Code: Select all
$result=mysql_query($sql) or die (mysql_error());
You overwrite the first $result inside the loop, so the second time through it doesn't work.
Re: Select * From Variable help
Posted: Fri Oct 24, 2008 12:29 pm
by TheBrandon
tasairis wrote:Outside the loop
Code: Select all
$result=mysql_query($sql) or die (mysql_error());
and then again
inside the loop
Code: Select all
$result=mysql_query($sql) or die (mysql_error());
You overwrite the first $result inside the loop, so the second time through it doesn't work.
Yeah I was worried about that once before, but when I change it I get this error:
Code: Select all
Warning: mysql_result(): Unable to jump to row 1 on MySQL result index 7 in /home/content/r/e/v/revivemedia/html/db/index-true.php on line 33
Here's what I have now:
Code: Select all
session_start();
if(!session_is_registered(myusername)){
header("location:index.php");
}
//get username
//$myusername=$_GET['id'];
//connect
db_connect();
//query
$sql="SELECT * FROM `clients` ORDER BY `client` ASC";
$result=mysql_query($sql) or die (mysql_error());
//stick our toe in the water
$totalpull = mysql_num_rows($result) or die (mysql_error());
$i=0;
while($totalpull > $i){
//Loop through client names
$array = mysql_fetch_array($result);
$clientname=($array["client"]);
echo $clientname;
echo "<br/>";
//select other db
//$fluid = "SELECT * FROM $tb2_surfers ORDER BY $tb2_id DESC LIMIT 1";
$sql = "SELECT * FROM $clientname";
$resultx=mysql_query($sql) or die (mysql_error());
$statusx = mysql_result($resultx,$i,"status");
$i++;
}
exit;
The first query is just to get how many clients I have. Once I know that, I don't really need it any more, data display wise. It's just "Okay, we have 3 clients, so we need to pull the status from the 3 tables for them" kind of thing. Each client has their own table, so its just:
"Find out how many clients we have, loop through the client list and pull the latest status and display it"
I was originally trying to do it with 1 table but I found the "Select each unique client with the latest ID once" kind of query a bit too complex, and thought giving them all their own table would be easier to get online since I really need to have this done ASAP.
Re: Select * From Variable help
Posted: Fri Oct 24, 2008 12:55 pm
by requinix
I don't get this part:
Code: Select all
$statusx = mysql_result($resultx,$i,"status");
$i is the client number from the clients table - what does that have to do with the individual clients?
Re: Select * From Variable help
Posted: Fri Oct 24, 2008 1:01 pm
by TheBrandon
tasairis wrote:I don't get this part:
Code: Select all
$statusx = mysql_result($resultx,$i,"status");
$i is the client number from the clients table - what does that have to do with the individual clients?
If I take the $i away, it doesn't output anything.
I only had it so it progressed. I suppose I don't need it though; I only want one status per client right now.
How should I change it?
I'm not even 100% sure what that does in the context of pulling data. I always assumed it added to the counter; query/row#/table or something. That way it would start with 1 row and progress until the end.
Do I misunderstand the way that works?
If I make it this:
Code: Select all
session_start();
if(!session_is_registered(myusername)){
header("location:index.php");
}
//get username
//$myusername=$_GET['id'];
//connect
db_connect();
//query
$sql="SELECT * FROM `clients` ORDER BY `client` ASC";
$result=mysql_query($sql) or die (mysql_error());
//stick our toe in the water
$totalpull = mysql_num_rows($result) or die (mysql_error());
$i=0;
while($totalpull > $i){
//Loop through client names
$array = mysql_fetch_array($result);
$clientname=($array["client"]);
echo $clientname;
echo "<br/>";
//select other db
//$fluid = "SELECT * FROM $tb2_surfers ORDER BY $tb2_id DESC LIMIT 1";
$sqlx = "SELECT * FROM $clientname";
$resultx=mysql_query($sqlx) or die (mysql_error());
$statusx = mysql_result($resultx,"status");
$i++;
}
exit;
I just get:
Thank you so much for helping me thus far.
Re: Select * From Variable help
Posted: Fri Oct 24, 2008 1:28 pm
by requinix
mysql_result is for retrieving a row at a specific position. If you just want the next row, use
_fetch_array,
_fetch_assoc,
_fetch_row, or
_fetch_object.
Like you did earlier with
Code: Select all
$array = mysql_fetch_array($result);
$clientname=($array["client"]);
Re: Select * From Variable help
Posted: Fri Oct 24, 2008 1:41 pm
by TheBrandon
You rule. I finally got it working.
Code: Select all
//connect
db_connect();
//query
$sql="SELECT * FROM `clients` ORDER BY `client` ASC";
$result=mysql_query($sql) or die (mysql_error());
//stick our toe in the water
$totalpull = mysql_num_rows($result) or die (mysql_error());
$i=0;
while($totalpull > $i){
//Loop through client names
$array = mysql_fetch_array($result);
$clientname=($array["client"]);
echo $clientname;
echo "<br/>";
//select other db
$sqlx = "SELECT * FROM $clientname LIMIT 1";
$resultx=mysql_query($sqlx) or die (mysql_error());
$array = mysql_fetch_array($resultx);
$status=($array["status"]);
echo $status;
echo "<br/>";
$i++;
}
exit;
Thank you so much, and thank you explaining the result thing to me.
Re: Select * From Variable help
Posted: Fri Oct 24, 2008 2:07 pm
by TheBrandon
Okay now I'm really confused. How come it won't work here?
Code: Select all
<?require_once('/home/content/r/e/v/revivemedia/html/db/inc/global.php');
session_start();
if(!session_is_registered(myusername)){
header("location:index.php");
}
$myclientname=$_GET['client'];
//output header
echo <<<HEADER
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "DTD/xhtml1-transitional.dtd">
<html>
<head>
<title>Client Database | Revive Media Services</title>
<style type="text/css">
@import "reset.css"; /* just some basic formatting, no layout stuff */
@import "style.css"; /* formatting */
</style>
</head>
<body>
<div id="leftcontent">
<div class="datatable">
<!--Labels-->
<div class="titlesone">Priority</div>
<div class="titlestwo">Client</div>
<div class="titlesthree">Last Status</div>
<div class="titlesfour">Date</div>
<div class="titlesfive">ID</div>
HEADER;
//connect
db_connect();
echo $myclientname;
//query
$sql="SELECT * FROM $myclientname ORDER BY id DESC";
$result=mysql_query($sql) or die (mysql_error());
//stick our toe in the water
$totalpull = mysql_num_rows($result) or die (mysql_error());
$i=0;
while($totalpull > $i){
//select other db
$sqlx = "SELECT * FROM $clientname ORDER BY id DESC";
$resultx=mysql_query($sqlx) or die (mysql_error());
//fetch data array
$array = mysql_fetch_array($resultx);
//assign variables
$status=($array["status"]);
$priority=($array["priority"]);
$priority=strtolower($priority);
$status = myTruncate2($status, 53);
$date=($array["date"]);
$name=($array["user"]);
$i++;
if ($priority==="normal"){
echo"<div class=\"prioritynormal\">$priority</div>";
}
if ($priority==="high"){
echo"<div class=\"priorityhigh\">$priority</div>";
}
if ($priority==="low"){
echo"<div class=\"prioritylow\">$priority</div>";
}
if ($priority==="none"){
echo"<div class=\"prioritynone\">$priority</div>";
}
echo <<<DATA
<!--Data-->
<div class="client"><a href="view-client.php?client=$clientname&id=$myusername">$clientname</a></div>
<div class="status"><a href="view.php?id=$id">$status</a></div>
<div class="date">$date</div>
<div class="id">$name</div>
<div class="clear"></div>
DATA;
}
echo <<<BOTTOM
</div>
</div>
<div id="rightcontent">
<ul>
<li><a href="index-true.php?id=$myusername">Home</a></li>
<li><a href="add.php">Add Update</a></li>
<li><a href="add-client.php">Add Client</a></li>
<li><a href="logout.php">Logout</a></li>
</ul>
</div>
</body>
</html>
BOTTOM;
?>
It works fine in SQL as "SELECT * FROM Children_of_Bodom ORDER BY id DESC LIMIT 0 , 30" but when I execute it through PHP I get:
Code: Select all
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ORDER BY id DESC' at line 1
It's the same code from the other page. What am I missing?
Re: Select * From Variable help
Posted: Fri Oct 24, 2008 2:16 pm
by TheBrandon
Nevermind, I got it. The $clientname and $myclientname variables were mismatched.
Re: Select * From Variable help
Posted: Fri Oct 24, 2008 2:59 pm
by TheBrandon
Ok so now it's just spitting out single characters.
Here's what I've got:
Code: Select all
<?require_once('/home/content/r/e/v/revivemedia/html/db/inc/global.php');
session_start();
if(!session_is_registered(myusername)){
header("location:index.php");
}
$myclientname=$_GET['client'];
//output header
echo <<<HEADER
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "DTD/xhtml1-transitional.dtd">
<html>
<head>
<title>Client Database | Revive Media Services</title>
<style type="text/css">
@import "reset.css"; /* just some basic formatting, no layout stuff */
@import "style.css"; /* formatting */
</style>
</head>
<body>
<div id="leftcontent">
<div class="datatable">
<!--Labels-->
<div class="titlesonefull">Priority</div>
<div class="titlesthreefull">Last Status</div>
<div class="titlesfourfull">Date</div>
<div class="titlesfivefull">ID</div>
<div class="clear"></div>
HEADER;
//connect
db_connect();
//query
$sql="SELECT * FROM $myclientname ORDER BY id DESC";
$result=mysql_query($sql) or die (mysql_error());
//stick our toe in the water
$totalpull = mysql_num_rows($result) or die (mysql_error());
$i=0;
while($totalpull > $i){
//select other db
$sqlx = "SELECT * FROM $myclientname";
$resultx=mysql_query($sqlx) or die (mysql_error());
//fetch data array
$arrayx = mysql_fetch_array($resultx);
foreach($arrayx as $array){
//assign variables
$status=($array["status"]);
$priority=($array["priority"]);
$priority=strtolower($priority);
$date=($array["date"]);
$name=($array["user"]);
if ($priority==="normal"){
echo"<div class=\"prioritynormal\">$priority</div>";
}
if ($priority==="high"){
echo"<div class=\"priorityhigh\">$priority</div>";
}
if ($priority==="low"){
echo"<div class=\"prioritylow\">$priority</div>";
}
if ($priority==="none"){
echo"<div class=\"prioritynone\">$priority</div>";
}
echo <<<DATA
<!--Data-->
<div class="clientfull"><a href="view-client.php?client=$clientname&id=$myusername">$clientname</a></div>
<div class="statusfull"><a href="view.php?id=$id">$status</a></div>
<div class="datefull">$date</div>
<div class="idfull">$name</div>
<div class="clear"></div>
DATA;
}
$i++;
}
echo <<<BOTTOM
</div>
</div>
<div id="rightcontent">
<ul>
<li><a href="index-true.php?id=$myusername">Home</a></li>
<li><a href="add.php">Add Update</a></li>
<li><a href="add-client.php">Add Client</a></li>
<li><a href="logout.php">Logout</a></li>
</ul>
</div>
</body>
</html>
BOTTOM;
?>
And this is what it gives me:
Code: Select all
1
1
1
1
1
1
A
A
A
A
A
A
n
n
n
n
n
n
S
S
S
S
S
S
1
1
1
1
1
1
1
1
1
1
1
1
A
A
A
A
A
A
n
n
n
n
n
n
S
S
S
S
S
S
Any ideas?
Re: Select * From Variable help
Posted: Fri Oct 24, 2008 3:09 pm
by requinix
Code: Select all
$arrayx = mysql_fetch_array($resultx);
foreach($arrayx as $array){
$arrayx is one array, with the fields from your query. It is
not an array of all the results.
Changing those two lines to
Code: Select all
while ($array = mysql_fetch_array($resultx)) {
should be the only fix you need.
Re: Select * From Variable help
Posted: Fri Oct 24, 2008 3:15 pm
by TheBrandon
Hmmm, it's working, but it's giving me the results twice.
Code: Select all
normal
Aliquam at arcu. Vivamus nunc. Sed pharetra elementum felis. Sed eget magna sed diam vulputate blandit. In hac habitasse platea dictumst. Nam in ante. Aenean vel nibh at eros egestas placerat. Sed tincidunt dictum mauris. Cras sodales dictum urna. Ut massa urna, condimentum eget, porttitor sed, hendrerit in, magna.
10.24.08
Shelley
normal
Children of Bodom RULE!
10.24.08
shelley
normal
Aliquam at arcu. Vivamus nunc. Sed pharetra elementum felis. Sed eget magna sed diam vulputate blandit. In hac habitasse platea dictumst. Nam in ante. Aenean vel nibh at eros egestas placerat. Sed tincidunt dictum mauris. Cras sodales dictum urna. Ut massa urna, condimentum eget, porttitor sed, hendrerit in, magna.
10.24.08
Shelley
normal
Children of Bodom RULE!
10.24.08
shelley
"Children of Bodom RULE!" is one entry and the latin is the other.
Also, was there any rhyme or reason to how many letters it would pull last time? Like, why stop at the capital S in "Sed"? Just wanting to understand.
Here's the code I'm using now, by the way:
Code: Select all
<?require_once('/home/content/r/e/v/revivemedia/html/db/inc/global.php');
session_start();
if(!session_is_registered(myusername)){
header("location:index.php");
}
$myclientname=$_GET['client'];
//output header
echo <<<HEADER
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "DTD/xhtml1-transitional.dtd">
<html>
<head>
<title>Client Database | Revive Media Services</title>
<style type="text/css">
@import "reset.css"; /* just some basic formatting, no layout stuff */
@import "style.css"; /* formatting */
</style>
</head>
<body>
<div id="leftcontent">
<div class="datatable">
<!--Labels-->
<div class="titlesonefull">Priority</div>
<div class="titlesthreefull">Last Status</div>
<div class="titlesfourfull">Date</div>
<div class="titlesfivefull">ID</div>
<div class="clear"></div>
HEADER;
//connect
db_connect();
//query
$sql="SELECT * FROM $myclientname ORDER BY id DESC";
$result=mysql_query($sql) or die (mysql_error());
//stick our toe in the water
$totalpull = mysql_num_rows($result) or die (mysql_error());
$i=0;
while($totalpull > $i){
//select other db
$sqlx = "SELECT * FROM $myclientname";
$resultx=mysql_query($sqlx) or die (mysql_error());
//fetch data array
while ($array = mysql_fetch_array($resultx)) {
//assign variables
$status=($array["status"]);
$priority=($array["priority"]);
$priority=strtolower($priority);
$date=($array["date"]);
$name=($array["user"]);
if ($priority==="normal"){
echo"<div class=\"prioritynormal\">$priority</div>";
}
if ($priority==="high"){
echo"<div class=\"priorityhigh\">$priority</div>";
}
if ($priority==="low"){
echo"<div class=\"prioritylow\">$priority</div>";
}
if ($priority==="none"){
echo"<div class=\"prioritynone\">$priority</div>";
}
echo <<<DATA
<!--Data-->
<div class="clientfull"><a href="view-client.php?client=$clientname&id=$myusername">$clientname</a></div>
<div class="statusfull"><a href="view.php?id=$id">$status</a></div>
<div class="datefull">$date</div>
<div class="idfull">$name</div>
<div class="clear"></div>
DATA;
}
$i++;
}
echo <<<BOTTOM
</div>
</div>
<div id="rightcontent">
<ul>
<li><a href="index-true.php?id=$myusername">Home</a></li>
<li><a href="add.php">Add Update</a></li>
<li><a href="add-client.php">Add Client</a></li>
<li><a href="logout.php">Logout</a></li>
</ul>
</div>
</body>
</html>
BOTTOM;
?>