|
PHP - Find The Table From A Sql Query.
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 Similar TutorialsHi all, Just a pretty basic question that I can't find the asnwer to: I have a MySQL query of the following form: Code: [Select] $result = mysql_query (" SELECT $cols FROM tablename WHERE......."); Where $cols is as follows: Code: [Select] $cols = firstname, lastname, $price1, $price2, etc. ($price1 and $price2 are variable column names that contains prices) How do I find the lowest value for $price1 and/or $price2 out of the results set of this query? Thanks! I have this query to show the current class I'm in: Code: [Select] $GetTimeTable = "SELECT * FROM timetable WHERE username='$username' AND Subject_date='$today_date'"; Then I have this to display current class: Code: [Select] if($TimeNow >= $Start_time && $TimeNow <= $End_time){ echo "Current Class: "; echo $Subject;} How would I find next class that day? Ultimate goal is for the query to find the row in the database to avoid the validation and carry on with the rest of the script. What actually happens is the script runs without any parse errors but it doesn't find the row within mysql. $query = sprintf('SELECT name_id, title, description, price, width, height, depth, quantity FROM art WHERE product_code = "$u"', mysql_real_escape_string($product_code, $db)); $result = mysql_query($query, $db)or die(mysql_error($db)); if (mysql_num_rows($result) != 1) { header('Location: home.php'); mysql_free_result($result); mysql_close($db); exit(); } I've echoed the where variable and the mysql_num_rows($result). the where variable is as expected but the result variable comes out as 0. I've checked my phpadmin and there is a entry in there. This worked fine before I sprinted the table. Any help appreciated. Hi guys I am new here I have had a search around and can't really find out how to do this. Its quite difficult for me to explain and I am really new to PHP / mySQL - just starting out really. I hope to learn from doing small projects like this and seeing how it gets done. So I have a mySQL DB with two different tables - one is called "notesnew" the other is called "users". I have modified a common tutorial on how to make a "to do list" for what I am doing here. Basically users can login and post messages on this list as them self. Each user's post appears as a list item, showing their username, the time they posted the message and the message content. The message content, time and owner of the message is stored in the "notesnew" table. The list of user's, their passwords and their "avatar/profile" image URL location is stored in the "users" table. Now I am having trouble getting each user's avatar image from being output next to each of their posts. The part that is confusing me is being able to match up the username's post with their content that is display - because I am posting the user's name along with their post, based on the logged in "session name" as their username. So basically, all the content being listed is being displayed from the "notesnew" table. I need to somehow figure out what each user's profile image is based on what is stored against their entry in the "users" table. Here is the code I have so far. It is not working properly - it displays all content etc, but displays the avatar / image of the last user that has registered. Can anyone help me out here with some code that works, or pointers to try follow? I know the $finduserprofile part is incorrect in the way it goes through all user's and finds their profileimg, and that the image displayed next to each post is therefore incorrect (as it displays the last member to have registered's avatar, but its just that I don't know how else to do what I am trying to do. Code: [Select] <?php //Connect to the database $connection = mysql_connect('localhost', 'zzzz' , 'zzzzzzz'); $selection = mysql_select_db('zzzzz', $connection); $username = $_COOKIE['ID_my_site']; //Was the form submitted? if($_POST['submit']){ //Map the content that was sent by the form a variable. Not necessary but it keeps things tidy. $content = $_POST['content']; //Insert the content into database $ins = mysql_query("INSERT INTO `notesnew` (content, owner, dp_time) VALUES ('$content', '$username', NOW())"); //Redirect the user back to the members or index page header("Location:index.php"); } /*Doesn't matter if the form has been posted or not, show the latest posts*/ //Find all the notes in the database and order them in a descending order (latest post first). $find = mysql_query("SELECT * FROM `notesnew` ORDER BY id DESC"); $finduserprofile = mysql_query("SELECT * FROM `users` ORDER BY id DESC"); //Setup the un-ordered list echo '<ul>'; while($row = mysql_fetch_array($finduserprofile)) { $imagelocation = $row['profileimg']; //Continue looping through all of them while($row = mysql_fetch_array($find)) { $owner = $row['owner']; //For each one, echo a list item giving a link to the delete page with it's id. echo '<li>' . '<img src ="' . $imagelocation . '">' . $row['owner'] . ' said at ' . $row['dp_time'] . ': ' . $row['content'] . ' <a id="' . $row['id'] . '" href="delete.php?id=' . $row['id'] . '"><img src="delete.png" alt="Delete?" /></a></li>'; } } //End the un-ordered list echo '</ul>'; ?> This topic has been moved to MySQL Help. http://www.phpfreaks.com/forums/index.php?topic=350064.0 Hi there I'm trying to work out how I'd go about searching my database for URLs that have been submitted by users that are duplicates. Now this isn't as simple as http://www.example.com/something.php and http://www.example.com/something.php oh no! People have entered links such as http://www.example.com//something.php or http://www.example.com///something.php The script (I didn't write it) currently see's these links as unique, but they're not. They function exactly the same way. Anyone have any advice on how to weed these out? I was thinking maybe a straight forward LIKE '%//%' in the mySQL syntax, but this will bring all URLs up due to the http:// aspect of the link Any feedback would be appreciated! Ok so standard query can be written as: SELECT * FROM my_table WHERE specific_field = 'some_term' Is there some query that I can search for 'some_term' in all the fields at once in my_table without defining them in the query? SELECT * FROM my_table WHERE any_field = 'some_term' 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 dont know if many of you have heard of bitcoins? its just a virtual currency with real value... I have got the daemon running for it, and have connected it to PHP. The code i am using to return all transactions is: Code: (php) [Select] print_r($bitcoin->listtransactions("")); echo "\n"; This will return: Quote Array ( => Array ( [account] => [address] => 1ALtJaqGiShyGMVNRNn77E67PuBKc9FLyo [category] => receive [amount] => 0.001 [confirmations] => 13 [txid] => 0f36872c60da25b29ff3a8efa91d931e26fe1a4354da280f3 d69234c8b521c8c [time] => 1314115260 ) [1] => Array ( [account] => [category] => move [time] => 1314115550 [amount] => -0.001 [otheraccount] => 4 [comment] => ) ) How could i output this info into a table instead of the above? Thanks 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> 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> 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> </p> <p> </p> <p> </p> <p> <?php mysql_close(); ?> </p> </div> Hi Guys, I have a query and a while loop output that I am sending to a webpage in a table format but I need some help in how best to do this so that the table columns line up with each other. Right now the column width seems to change depending on the data in the row. The code is quite simple: <?php include("lib/config.php"); $query = "SELECT * FROM staff_member"; $result = mysql_query($query); ?> <html> <style type="text/css"> <!-- @import url(style/style.css); --> </style> <head> </head> <body> <h2 align="center"> Full Staff list with Dependants</font></h2> <?php while($row = mysql_fetch_object($result)) { echo "<table id=sample> <th>Title</th> <th>First Name</th> <th>Initial</th> <th>Last</th> <th>Agency</th> <th>Street</th> <th>Address</th> <th>Parish</th> <th>Country</th> <th>Office Tel#</th> <th>Home Tel#</th> <th>Office Mobile#</th> <th>Personal Mobile#</th> <th>Zone</th> <th>Status</th> <th>Location</th> <th>Updated by</th> <tr> <td>" .$row->title ."</td> <td>" .$row->first ."</td> <td>" .$row->initial ."</td> <td>" .$row->last ."</td> <td>" .$row->agency ."</td> <td>" .$row->street ."</td> <td>" .$row->adress ."</td> <td>" .$row->parish ."</td> <td>" .$row->country ."</td> <td>" .$row->officetel ."</td> <td>" .$row->hometel ."</td> <td>" .$row->officemobile ."</td> <td>" .$row->personalmobile ."</td> <td>" .$row->zone ."</td> <td>" .$row->status ."</td> <td>" .$row->location ."</td> <td>" .$row->updater ."</td> </tr>"; echo "</table> </br>"; }//close $row while ?> </body> </html> Any help appreciated! 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 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 = < && > "); 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. 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
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>
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} |
|||||||