Select * From Variable help

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

TheBrandon
Forum Commoner
Posts: 87
Joined: Tue May 20, 2008 8:55 am

Select * From Variable help

Post 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.
oscardog
Forum Contributor
Posts: 245
Joined: Thu Oct 23, 2008 4:43 pm

Re: Select * From Variable help

Post by oscardog »

Code: Select all

 
$sql = "SELECT * FROM '$clientname' ";
 
I think
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Select * From Variable help

Post by requinix »

" and ' mark strings.
` marks objects.

Code: Select all

"SELECT * FROM `$clientname`"
What's the error message, and have you made sure $clientname is a table?
TheBrandon
Forum Commoner
Posts: 87
Joined: Tue May 20, 2008 8:55 am

Re: Select * From Variable help

Post 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.
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Select * From Variable help

Post 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.
TheBrandon
Forum Commoner
Posts: 87
Joined: Tue May 20, 2008 8:55 am

Re: Select * From Variable help

Post 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.
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Select * From Variable help

Post 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?
TheBrandon
Forum Commoner
Posts: 87
Joined: Tue May 20, 2008 8:55 am

Re: Select * From Variable help

Post 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:

Code: Select all

Children_of_Bodom
Testing
Thank you so much for helping me thus far.
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Select * From Variable help

Post 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"]);
TheBrandon
Forum Commoner
Posts: 87
Joined: Tue May 20, 2008 8:55 am

Re: Select * From Variable help

Post by TheBrandon »

tasairis wrote: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"]);
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.
TheBrandon
Forum Commoner
Posts: 87
Joined: Tue May 20, 2008 8:55 am

Re: Select * From Variable help

Post 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?
TheBrandon
Forum Commoner
Posts: 87
Joined: Tue May 20, 2008 8:55 am

Re: Select * From Variable help

Post by TheBrandon »

Nevermind, I got it. The $clientname and $myclientname variables were mismatched.
TheBrandon
Forum Commoner
Posts: 87
Joined: Tue May 20, 2008 8:55 am

Re: Select * From Variable help

Post 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?
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Select * From Variable help

Post 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.
TheBrandon
Forum Commoner
Posts: 87
Joined: Tue May 20, 2008 8:55 am

Re: Select * From Variable help

Post 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;
?>
Post Reply