PHP - Query Any Fileds In The Table

Hello,
I want to know which is the table in the sql SELECT query.
What i have done to take fields :
Code: [Select]
$temp_sql = strtolower($sql);
$pos = stripos($temp_sql, 'select');
$str = substr($temp_sql, $pos);
$str_two = substr($str, strlen($start));
$second_pos = stripos($str_two, 'where');
$temp_fields = substr($str_two, 0, $second_pos);
$fields = trim($temp_fields);
But for table i don't know what to do. After the table can end the string or can be a WHERE or ORDER .
Is there any way to take it.
Until now i use
$temp = explode(' ',$sql);
$table= $temp[3];
Thank you

I'm having trouble with the logic for my query. Printing $sql gives me the following:

SELECT r.id,r.created,r.firstname,r.lastname,r.address1,r.city,r.state,r.zip,r.phone,r.email,r.comments,Error: 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 'BBQ' at line 1

"BBQ" is the first $val from the loop. What am I missing?

Code: [Select]
      $sql = "SELECT r.id,r.created,r.firstname,r.lastname,r.address1,r.city,r.state,r.zip,r.phone,r.email,r.comments,";

            $dbQuery = mysql_result(mysql_query("SELECT description FROM events"), 0,0);           
        $size = count($dbQuery);           
        $i = 0;
            foreach ($dbQuery as $val)
            { //02
           if($val != '')
            { //02           
                       $sql .= "MAX(CASE e.description WHEN '". $val ."' THEN e.description END) AS `". $val ."`";                 
                  }
                  $i++;
          if($i != $size) $sql .= ",";
                 
            }//02
       
            if (!mysql_query($dbQuery)) {
                  die('Error: ' . mysql_error());
            }

     $sql .= "FROM requests r
                   LEFT OUTER JOIN (
                         registration_xref xref
                        INNER JOIN events e ON e.id_events = xref.event_id
                   ) ON r.id = xref.attendee_id
                   GROUP BY r.id";

Hi guys,
I need your help!
I have two tables "sp_users" and "sp_schools" in the same database.
Now I want to add column "time" from "sp_schools" to a phpfile which displays "sp_users".
The column "time" should be after column "update".

I made it upto here, but to get the second query and display
Please help. I am devastated...

Code: [Select]
<?php
$host="xxx"; // Host name
$username="xxx"; // Mysql username
$password="xxx"; // Mysql password
$db_name="xxx"; // Database name
$tbl_name="sp_users"; // Table name

// Connect to server and select database.
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");

$sql="SELECT * FROM $tbl_name ORDER BY user_id";
$result=mysql_query($sql);
?>
<style type="text/css">
<!--
.style2 {font-weight: bold}
.style3 {
font-family: Arial, Helvetica, sans-serif;
color: #000033;
}
.style8 {font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 10px; color: #003333; }
-->
</style>

<title>User overview</title><table width="486" border="0" align="center" cellpadding="0" cellspacing="1">
<tr>
<td width="427">
 
    <div align="left">
      <table width="486" border="1" cellspacing="0" cellpadding="3">
        <tr>
          <td colspan="4"><div align="center" class="style1 style3"><strong>SchoolPorta Users </strong></div></td>
        </tr>
        <tr>
          <td width="26" align="center"><span class="style2">id</span></td>
          <td width="70" align="center"><span class="style2">Name</span></td>
          <td width="114" align="center"><span class="style2">Lastname</span></td>
          <td width="146" align="center"><span class="style2">Email</span></td>
          <td width="88" align="center"><span class="style2">Update</span></td>
        </tr>
        <?php
while($rows=mysql_fetch_array($result)){
?>
        <tr>
          <td><span class="style8"><? echo $rows['user_id']; ?></span></td>
          <td><span class="style8"><? echo $rows['user_first_name']; ?></span></td>
          <td><span class="style8"><? echo $rows['user_surname']; ?></span></td>
          <td><span class="style8"><? echo $rows['user_login']; ?></span></td>
          <td align="center"><a href="update.php?id=<? echo $rows['user_id']; ?>" class="style8">update</a></td>
        </tr>
        <?php
}
?>
      </table>
    </div></td>
</tr>
</table>
<div align="center">
  <?php
mysql_close();
?>
</div>

Hello   We have a database table that confirms the installations started and completed for our game... and I am looking to confirm how many installs start but never complete...   So, this is my current query...

SELECT description, ip from error_log where description like '%install%' order by ip;

 

Hi guys I need your help,

I am trying already for days to sort it out but does not work.
My file "pback.php" displays the mysql table "sp_users". I want to add the columns "school_name" and "school_address" from another table "sp_schools" (same database) at the right end of the displayed table in "pback.php" (after the column update)
I tried with leftjoin but it does not work.
Please help me. I am getting really frustrated.
Thank you...

Code: [Select]
<?php
$host="xxx"; // Host name
$username="xxx"; // Mysql username
$password="xxx"; // Mysql password
$db_name="xxxx"; // Database name
$tbl_name="sp_users"; // Table name

// Connect to server and select database.
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");

$sql="SELECT * FROM $tbl_name ORDER BY user_id";
$result=mysql_query($sql);
?>
<style type="text/css">
<!--
.style2 {font-weight: bold}
.style3 {
font-family: Arial, Helvetica, sans-serif;
color: #000000;
}
.style10 {font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 10px; color: #000000; }
-->
</style>

<title>User overview</title><table width="486" border="0" align="left" cellpadding="0" cellspacing="1" bgcolor="#996600">
<tr>
<td width="427">
 
    <div align="left">
      <table width="486" border="1" cellspacing="0" cellpadding="3">
        <tr>
          <td colspan="4"><div align="center" class="style1 style3"><strong>SchoolPorta.com Users </strong></div></td>
        </tr>
        <tr>
          <td width="26" align="center"><span class="style2">id</span></td>
          <td width="70" align="center"><span class="style2">Name</span></td>
          <td width="114" align="center"><span class="style2">Lastname</span></td>
          <td width="146" align="center"><span class="style2">Email</span></td>
          <td width="88" align="center"><span class="style2">Update</span></td>
        </tr>
        <?php
while($rows=mysql_fetch_array($result)){
?>
        <tr>
          <td><span class="style10"><? echo $rows['user_id']; ?></span></td>
          <td><span class="style10"><? echo $rows['user_first_name']; ?></span></td>
          <td><span class="style10"><? echo $rows['user_surname']; ?></span></td>
          <td><span class="style10"><a href="mailto:<?php echo $rows['user_login']; ?>"><?php echo $rows['user_login']; ?></a></span></td>
          <td align="center"><a href="update.php?id=<? echo $rows['user_id']; ?>" class="style10">update</a></td>
        </tr>
        <?php
}
?>
      </table>
    </div></td>
</tr>
</table>

<div align="left">
  <p>&nbsp;  </p>
  <p>&nbsp;</p>
  <p>&nbsp;</p>
  <p>
    <?php
mysql_close();
?>
  </p>
</div>

Hi, I have a fantasy football website, and on a user account page I want to display fixtures that are coming up that include teams that the current user has chosen. My test_teams table stores all the team names and their teamid. The test_selections table is where each users team selections are stored, it has two columns, userid and teamid. The test_fixtures table has two columns, hometeam and awayteam, these two cloumns hold the teamid of the teams that are playing.

The code below correctly displays the fixtures that contain any of the current users team selections. However, it is only displaying the teamid of the teams that are playing as they have not been matched to the test_teams table to get the team name. Does anybody now how I can do this? I believe it can be done using a left join but so far I just keep getting errors when i try to write the code. Any help would be very much appreciated.

Code: [Select]
<table width="380" border="0">
<?php
$query = "SELECT test_fixtures.competition, test_fixtures.date, test_fixtures.hometeam, test_fixtures.awayteam FROM test_fixtures, test_selections WHERE test_selections.userid = '{$_SESSION['userid']}' AND (test_selections.teamid = test_fixtures.hometeam OR test_selections.teamid = test_fixtures.awayteam)";
        $result = mysql_query($query) or die(mysql_error());
        
        while($row = mysql_fetch_assoc($result))
{
?>
<tr>
<td width="85" class="fixtures_date"><?php echo $row['date']; ?></td>
<td width="30" class="fixtures_comp"><?php echo $row['competition']; ?></td>
<td width="135" class="fixtures_home_teams"><?php echo $row['hometeam']; ?></td>
<td width="25" class="fixtures_center">v</td>
<td width="135" class="fixtures_away_teams"><?php echo $row['awayteam']; ?></td>
</tr>
<?php
}
?>
</table>

Hi All

I have been trying to solve my query problem for days with no luck. Please help

I'm trying to build a pivot table query from 2 part query in php.

Both query runs ok in mysql query browser but once I place the query into php it doesn't work.

 

Here is the my code

I have a table I want to turn into a pivot table

+------+---------+-------------+--------+
| id   | product | salesperson | amount |
+------+---------+-------------+--------+
|    1 | radio   | bob         | 100.00 |
|    2 | radio   | sam         | 100.00 |
|    3 | radio   | sam         | 100.00 |
|    4 | tv      | bob         | 200.00 |
|    5 | tv      | sam         | 300.00 |
|    6 | radio   | bob         | 100.00 |
+------+---------+-------------+--------+

//Building First Part of the Query to get all the columns
    $sql = "SELECT DISTINCT
  CONCAT(',SUM(IF(salesperson = "',salesperson,'",1,0)) AS `',salesperson,'`')
  AS countpivotarg
FROM  sales
WHERE salesperson IS NOT NULL ";


 
    $result = $mdb2->query($sql);
    $count = $result->numRows();


       if ($count>0){
 
       while ($row = $result->fetchRow()){


   $q .=  $row['countpivotarg']

   }


   }


/*This should give the following results to plug into the next query statement*/,
,SUM(IF(salesperson = "bob",1,0)) AS `bob`
,SUM(IF(salesperson = "sam",1,0)) AS `sam`


//Plug first query results into a second query statement//

$sql = 'SELECT product '  . $q .  ',COUNT(*) AS Total
       FROM sales
        GROUP BY product WITH ROLLUP;';


 
    $result = $mdb2->query($sql);
    $count = $result->numRows();


       if ($count>0){
 
       while ($row = $result->fetchRow()){


    echo "rows here";

   }
   }
 




 
//Results
+---------+------+------+-------+
| product | bob  | sam  | Total |
+---------+------+------+-------+
| radio   |    2 |    2 |     4 |
| tv      |    1 |    1 |     2 |
| NULL    |    3 |    3 |     6 |
+---------+------+------+-------+



Any help would be greatly appreciated.

Thanks

Hi,

I am trying to store a sql query in a database, but every time I try to read it, it shows all the code as text. eg just print ' . $parts_row['part_number'] . '

Here is the code on the page:       

    $parts_sql = "SELECT * FROM parts WHERE part_cat = " . $category_row['cat_id'] . " ORDER BY CAST(part_a AS DECIMAL(10,2))";
  			$parts_result = mysql_query($parts_sql);
  
  			if(!$parts_result)
  			{
  				echo '<tr><td>The parts could not be displayed, please try again later.</tr></td></table>';
  			}
  			else
 			{
 
 				while($parts_row = mysql_fetch_array($parts_result))
 				{
                                               $pageformat_sql = "SELECT * FROM category_pages WHERE catpage_number = " . $category_row['cat_page'] . "";
 
                                               $pageformat_result = mysql_query($pageformat_sql);
 
 
                                                if(!$pageformat_result)
                                                {
                                                echo 'Error';
                                                }
                                                 else
                                                 {
                                                 while($pageformat_row = mysql_fetch_assoc($pageformat_result))
                                                 {
                                                   $data = $pageformat_row['catpage_table'];
                                                 echo '<table class="table2 centre" style="width:750px">
 	                                              ' . $pageformat_row['catpage_tabletitle'] . '' . $data . '';
 
 
 
                                                 }
                                                 }
 
     }}

And this is what is stored in the database table:

 <tr>
 <td class="cell left">' . $parts_row['part_number'] . '</td>
 <td class="cell centre">' . $parts_row['part_a'] . ' mm</td>
 <td class="cell centre">' . $parts_row['part_b'] . ' mm</td>
 <td class="cell centre">' . $parts_row['part_c'] . ' mm</td>
 <td class="cell centre">' . $parts_row['part_d'] . ' mm</td>
 <td class="cell centre">' . $parts_row['part_e'] . ' mm</td>
 <td class="cell centre">' . $parts_row['part_imped100'] . '</td>
 <td class="cell centre"><a href="datasheets/' . $parts_row['part_datasheet'] . '.pdf"><img border="0" src="images/pdf.jpg" alt="Download"</a></td></tr>

I would be very grateful to anyone who can help me with this.

Hello, 
I know this should be pretty simple to figure out, but everything I try is giving me absolutely no results. 
I have a mysql query selecting columns from my database and returning results. I have the results printing out right now, so I can see that this part is working. 

All I want to do is take the results and put them into a table to display on my page. Basically, take what's in the database table and copy it to a table I can put on the web. 

FYI I am using sourcerer so the "connect" code is taken care of for me in the "JFactory" bit of code. Here is the first part of my code, selecting the information from the database. 

{source} 
<?php 
$db = JFactory::getDbo(); 
$query = $db -> getQuery(true); 

$query -> SELECT($db -> quoteName(array('first_dept_name', 'last_name', 'dept', 'position', 'phone_num'))); 
$query -> FROM ($db -> quoteName('#__custom_contacts')); 
$query -> ORDER ('first_dept_name DESC'); 
$query -> WHERE ($db -> quoteName('contact_category')."=".$db -> quote('YTown Employees')); 
$db -> setQuery($query); 
$results = $db -> loadObjectList(); 

print_r($results); 

Here is where I am trying to print the results into a table. I got this code directly from a PHP book, but I am getting nothing at all returned back to me. I get table headers, but no data. 

<?php 

echo "<table><tr><th>Name</th><th>Department</th></tr>"; 

while ($row = mysqli_fetch_array ($result)){ 

echo "<tr>"; 

echo "<td>".$row['last_name']."</td>"; 

echo "<td>".$row['dept']."</td>"; 

echo "</tr>"; 

} 

echo "</table>"; 

?> 
{/source}

This serves more or less a meaningless purpose for my testing, but I've been trying to echo out the names of the tables that are being used in my UPDATE and SELECT queries and I have yet found a way to do this.. Did some research on php.net and Google and did not find anything that seems to directly address this..

My guess is it's so stupid simple, I probably will have an answer by the time I wake up..

But id there are any suggestions on this, Id be glad to take.

This topic has been moved to MySQL Help.

http://www.phpfreaks.com/forums/index.php?topic=305991.0

hi im trying to use  query to select from a table where the price is between to values that the user has to input such as min of 3 and max of 8 and return all the fields from the database where it is true im unsure how to do this but this is what i have so far   $res = pg_query ($conn, "SELECT ref,artist,composer,genre,title,album,label,price,description FROM music WHERE price = < && > ");

Given the following query,

SELECT t1.a, t1.b, t2.c FROM t1 INNER JOIN t2 ON t2.id=t1.t2_id WHERE t1.pk=123;

I get the following three records:

array(
  array('a'=>1,'b'=>2,'c'=>4),
  array('a'=>1,'b'=>2,'c'=>5),
  array('a'=>1,'b'=>2,'c'=>8)
)

What would be the best way to get just one record such as the following?

array('a'=>1,'b'=>2, 'c'=>array(4,5,8))

My thoughts were to use MySQL's GROUP_CONCAT, and then use implode() to turn it into an array, but didn't know if there was a better way.   Thanks

hi, I'm working with a script I've written (with a *lot* of help!!) I'm trying to get the results of a db search to be displayed in a html table, with a row for each result. I'm almost there, I've got 1 glitch and 1 cosmetic issue I can't resolve with the below script, any help would be greatly appreciated!!

1. the table displays the entire contents of the db before it is filtered through the search, I think this has something to do with the $num=mysql_numrows($result); expression, but I'm not sure how to fix it

2. I'd like the last column of the table to be about twice as wide as the others, as it contains a lot of free text, would I have to set the length of each column in order to do this or is there a shorthand way?

the current script is:

<form method="post" name="Search" action="test2.php">
<input type="text" name="name" autocomplete="OFF" />
<input value="Search" type="submit" name="Search" />
<input value="yes" type="hidden" name="submitted" />
</form>


<?php
if($_POST['submitted'] == 'yes')

$username="*****";
$password="*****";
$database="*****";
$server="localhost";
$db_handle = mysql_connect($server, $user_name, $password);
$db_found = mysql_select_db($database, $db_handle) or die( "Unable to select database");


$query="SELECT * FROM ***** WHERE surname LIKE '" . mysql_real_escape_string($_POST['name']) . "%'";
$result=mysql_query($query) or die ('<br>Query string: ' . $SQL . '<br>Produced error: ' . mysql_error() . '<br>');

if (mysql_num_rows($result) == 0) {
echo "No results found";
exit;
}

$num=mysql_numrows($result);


$fields_num = mysql_num_fields($result);
echo "<h1>Table: {$table}</h1>";
echo "<table border='1'><tr>";
for($i=0; $i<$fields_num; $i++)
{
    $field = mysql_fetch_field($result);
    echo "<td>{$field->name}</td>";
}
echo "</tr>\n";

while($row = mysql_fetch_row($result)){
    echo "<tr>";
foreach($row as $cell)
        echo "<td>$cell</td>";
    echo "</tr>\n";
}
mysql_free_result($result);
mysql_close($db_handle);
?>

Hello, I seem to have some problem with my script that has a goal of outputting data about the file size when a filename is queried.   The sql table name is file The table columns are as followed:  id | name | mime | size   The file name is stored in name. The script that i have that gets the file name is:

<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">

	<head>
		<meta http-equiv="content-type" content="text/html;charset=utf-8" />
		<meta name="generator" content="Adobe GoLive" />
		<title>File Select</title>
					
	<!--The following script tag downloads a font from the Adobe Edge Web Fonts server for use within the web page. We recommend that you do not modify it.--><script>var __adobewebfontsappname__="dreamweaver"</script><script src="http://use.edgefonts.net/aguafina-script:n4:default.js" type="text/javascript"></script>
</head>

	 <body>
			
		<div id="title">
			  <h3 align="center">File Upload</h3>
			</div>
  <form action="result.php" method="post" name="fileID" target="_self" class="inp" AUTOCOMPLETE="ON">       
 <h1>
 
<!--Input file name-->

	<label for="fileID">File Name: </label>
              <input type="text" name='file1' id='sampleID' list="samp"> </input><br>
             <datalist id="samp">
 
<?php
$connect = mysql_connect('localhost', 'root', '');


mysql_select_db("test_db");
$query = mysql_query("SELECT * FROM  `file` ORDER BY  `file`.`name` ASC LIMIT 0 , 30");
WHILE ($rows = mysql_fetch_array($query)):
   $File_name = $rows['name'];  
  
echo "<option value=$File_name>$File_name/option> <br>";
endwhile;
?>
</datalist>

<input type="submit" class="button" >
</form>
</body>
</html>