|
PHP - Need Help Creating A Simple Php Sql Query Page
OK, here it is. I have been trying to do this myself, but it has been driving me insane and I turn to professionals here for help.
I am a basic web developer for a company that I work for in other capacities. I have a reasonable understanding of HTML and that is about where my expertise ends. I am not typically a programmer, just a simple (extremely) part time designer that uses Muse and Dreamweaver when necessary.
However, recently my company has asked me to accomplish a task for their website. In plain English, they want a large database that exists currently as a CVS file made into a searchable web page. It is 21 columns by approximately 6,700 rows.
To explain what I need a little more technically, here are my ideas and where I have gotten to so far:
1. The company uses godaddy, into which I *believe* I have successfully imported the spreadsheet. I believe it is successful because through godaddy's SQL Control Panel (phpMyAdmin console), I can do the EXACT searches that the company needs, and it spits out the EXACT results that I need.
2. The end result needs to be a .php that I can upload to the website's root folder that can be then inserted into premade pages using:
<iframe src="SMQ.php" scrolling="yes" width="950" height="800"></iframe>3. On the .php page, I need to have a way to log in to the SQL server and a simple search box built in that will allow the user to input a very simple search string consisting of no more than 4 numbers and 3 letters at a time. No buttons, no check boxes, just a search box. 3. This query then needs to be output as a nice data table, similar to this: This in fact is a screenshot of a search I performed out of my SQL database, in phpMyAdmin using the column "Scott" for the search, and the number 226 as the search term. All column names are visible with the exception of the first column, entitled LINEID, made to be the key, and the output should not include the key but have everything else as above. 4. I can see what the simple line of php is that performed this task: SELECT * FROM `SMQSQL` WHERE `Scott` = '226' ORDER BY `LINEID` ASCbut I can't figure out how the hell to get this incorporated to a .php search. To sum it up, I need a .php page written that can connect to a SQL database, perform a data based search, and spit out a clean table when it is done. I had accomplished this in the past using an import into google docs and using it to perform a search and result display via the following code built into a php called SMQ.php:
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Example of Google Spreadsheet Data Visualisation</title>
</head>
<body>
<form id="form1" method="post" action ="<?php echo $_SERVER['PHP_SELF']; ?>"> <label>
<input id="search" name="search" type="text" />
</label>
<label>
<input id="Scott #" name="Scott #" type="submit" value="Scott #" />
</label>
<img src="loading.gif" width="16" height="11" />
</form>
<p>
<?php
$search= $_REQUEST['search'];
if ($search > ''){ $search = $search;} else { $search = '';}
?>
<script type="text/javascript" src="http://www.google.com/jsapi"></script>
<script type="text/javascript">
google.load('visualization', '1', {packages: ['table']});
</script>
<script type="text/javascript">
var visualization;
function drawVisualization() {
var query = new google.visualization.Query(
'https://docs.google.com/spreadsheet/ccc?key=0AronCwm9QPefdGpIUllscGgtLUJod2pOazc0bjU0cUE&usp=sharing');
query.setQuery('SELECT A, B, C, D, E, F, G, H, I, J, K, L, M, N, O ,P ,Q ,R ,S ,T WHERE (A) LIKE ("<?php echo $search; ?>") order by A asc label A "Scott #", B "Den", C "Color", D "Cond", E "40", F "60", G "70", H "70J", I "75", J "75J", K "80", L "80J", M "85", N "85J", O "90", P "90J", Q "95", R "95J", S "98", T "98J"');
query.send(handleQueryResponse);
}
function handleQueryResponse(response) {
if (response.isError()) {
alert('Error in query: ' + response.getMessage() + '' + response.getDetailedMessage());
return;
}
var data = response.getDataTable();
visualization = new google.visualization.Table(document.getElementById('table'));
visualization.draw(data, { page: 'enable', page: 16, pageSize: 16, legend: 'bottom'});
}
google.setOnLoadCallback(drawVisualization);
</script>
<div id="table"></div>
</div>
</body>
</html>
But as you can see, this may not be the most secure thing in the world, plus we want to be able to expand it in the future and not be so simplistic, hence the need to switch to SQL.
Please let me know right away by contacting me at disead@gmail.com if this is something YOU might be able to help with. I'm sure for an experienced programmer, once you have the details from me that you need, it would take maybe 10 minutes to write. I don't have much, but I can pay a little bit for this one time job. If it ends up working out, I may be able to pay more down the line for more advanced options such as being able to do drop-down searches based on the column titled "ISSUE", as well as more things down the line as it grows.
Thank you so much, I hope to hear from someone soon!!!
Similar TutorialsHi, I want to develop array like following Code: [Select] $BCD=array('type' =>'TYPE1', array( 0=>array('column1'=>'value1','column2'=>'value1','column3'=>'value1','column4'=>'value1','column5'=>'value1','column6'=>'value1','column7'=>0), 1=>array('column1'=>'value1','column2'=>'value1','column3'=>'value1','column4'=>'value1','column5'=>'value1','column6'=>'value1','column7'=>0), 2=>array('column1'=>'value1','column2'=>'value1','column3'=>'value1','column4'=>'value1','column5'=>'value1','column6'=>'value1','column7'=>0), 3=>array('column1'=>'value1','column2'=>'value1','column3'=>'value1','column4'=>'value1','column5'=>'value1','column6'=>'value1','column7'=>0), ) )); I have written following code to achieve the same but not getting result. Code: [Select] $sql = "select * from tablename "; $result = mysql_query($sql); $k=0; while ($row = $db->mysql_fetch_array($result)) { // array_push($BCD['type'],$row['type']); $BCD1=array('type' =>$row['type'], array( $k=>array('column1'=>$row['column1'],'column2'=>$row['column2'],'column3'=>$row['column3'],'column4'=>$row['column4'], 'column5'=>$row['column5'],'column6'=>$row['column6'],'column7'=>$row['column7']) )); $k++; } helloi'm desperately trying to create a simple chatbox for my website using php and whateverthe logged in user posts to there box then gets wrote to a sql database called km_babble and then displayed in the room for others to read. the max number of posts per box should be like 15 or so .. can someone assist me or lead me to docs that will help? thanks much in advance Hi guys, I'm currently in the process of making a simple social networking type site for a uni project and have basically everything i want except the ability to use AJAX to link to my profiles in the search bar. I have this code to generate my XML: Code: [Select] <?php // create simplexml object $xml = new SimpleXMLElement('<rss version="2.0"></rss>'); // add channel information $xml->addChild('channel'); $xml->channel->addChild('title', 'The Social Network'); $xml->channel->addChild('link', 'http://reecesayer.com/projects/template/XML/news.rss'); $xml->channel->addChild('description', 'example'); // query database for article data include("phpfunctions.php"); db_connect(); $link = "www.reecesayer.com/projects/template/testprofile.php?id="; $select='SELECT email, id FROM users'; $result = mysql_query($select) or die(mysql_error()); while ($row = mysql_fetch_assoc($result)) { // add item element for each article $item = $xml->channel->addChild('item'); $item->addChild('email', $row['email']); $item->addChild('link', $row['id']); //I wanted to add a link before the $row['id'] } // save the xml to a file $xml->asXML('profiles.xml'); header ('Location: http://reecesayer.com/projects/template/XML/profiles.xml'); ?> I need to add a partial URL (http://reecesayer.com/projects/template/testprofile.php?id=) before the row is called so that when the email is searched it provides a link to the profile depending upon the id (http://reecesayer.com/projects/template/testprofile.php?id=8) Is there an easier way to do this or am i going the long way around? I'm only storing the data in XML as i saw a tutorial for a livesearch script on another site. Cheers, Reece Hi. I am trying to create a simple video file server. I finished the skeleton of it but when it came to creating the multitude of pages I realized that there could be an easier way to do it.
I started out basically getting the folder names in the server then printing them on the page.
<?php
$dirs = glob("*", GLOB_ONLYDIR);
echo '<ul>';
foreach($dirs as $dir)
{
$forbidden_folders = array("not4u", "ignore", Styles);
$filename = str_replace($forbidden_folders, '', $dir);
if (!empty($filename))
{
echo '<li><a href="'.$dir.'">'.$dir.'</a></li>';
}
}
echo '</ul>'
?>
Then I created in each of the folders with files a php file with this code:
<?php
function date_sort_desc($a, $b)
{
preg_match('/\w+ \d{4}/', $a, $matches_a);
preg_match('/\w+ \d{4}/', $b, $matches_b);
$timestamp_a = strtotime($matches_a[0]);
$timestamp_b = strtotime($matches_b[0]);
if ($timestamp_a == $timestamp_b) return 0;
return $timestamp_a < $timestamp_b;
}
$files = array();
$dir = opendir('.');
while(false != ($file = readdir($dir))) {
if(($file != ".") and ($file != "..") and ($file != "index.php")) {
$files[] = $file;
}
}
natsort($files);
$i = 0;
foreach($files as $file) {
$i++;
$string = str_replace("TV Show Name S1 E$i - ", '' , $file);
echo '<div><a href="../Discriptions/'.$file.'.php">'.basename($string, '.m4v').'</a></div><br>';
}
?>
This opens up a php file with the same file name as the file to be played containing the episode's thumbnail and description. That file then contains a link pointing back to the real file. The problem here is that I'd have to make a new php file for every file in my collection. I'm wondering if there's somehow a way to simplify all of this.
Edited by chrisyroid, 11 August 2014 - 12:13 AM. SELECT * FROM Sold WHERE substr(sold_date, 0, 4) = 2010 What I am trying to do is select all rows from the database from 2010, from the "sold_date" field. How is this accomplished with date values? (ex: "2010-08-11", "2009-01-15") i have a cell in my database like Code: [Select] Stats 100-10-3 and i want to update that with my code Code: [Select] $details = 100 . '-' . 10 . '-' . 3; $sql = "UPDATE usertable SET uSkillsMax=$details"; but my database updates instead of 100-10-3 it says 87? which is doing the math? any way to fix this? I've got to be missing something pretty basic here.. considering the query is pretty basic. I'm trying to figure out how to pull a query as an array so I can compare it against another array (array_diff) I'm doing a mysql_fetch_array, and I'm getting an error ( mysql_fetch_array(): supplied argument is not a valid MySQL result resource): Quote $checker = "SELECT ID FROM edible_uses"; $result2 = mysql_fetch_array($checker) or die(mysql_error()); //echoing to see if I'm getting what I need. echo $row['ID']; I've done a mysql_query and I get results. The table name and all that is correct. I'm stumped. This seems like a pretty simple query? I tried mysql_fetch_assoc as well. Same result? I tried it with an extra set of parenthesis around it. nope. The following mysql query is not returning rows like I expect it to. '$update_field' is a variable, matching an actual field name in table 'users'. 'user_task[1]' is an integer value. What am I missing here? Code: [Select] $query_update_user = "UPDATE users SET ".$update_field." = 'Y' WHERE user_no = '".$user_task[1]."'"; Hi -- This query seems to be problematic because the UPDATE is not being performed. Could you please take a gander and let me know what is the problem? BTW, the table "teamy" does contain 630 records. Thanks in advance! $sql = "SELECT COUNT( * ) AS records FROM teamy" ; $result = mysql_query( $sql ) ; if ( ! $result ) { die ( __line__ . "_teamy_" . mysql_error() ) ; } $a_count = mysql_fetch_assoc($result); if ( $a_count['records'] = 0 ) { echo "No records found in teamy." ; } else { /*** Update r_rost_rma ***/ $sql = "UPDATE r_rost_rma JOIN teamy ON r_rost_rma.student_id = teamy.student_id SET r_rost_rma.teachername = teamy.team WHERE RTRIM( UPPER( r_rost_rma.localcourse ) ) = 'HOMEROOM'" ; $result = mysql_query ( $sql ) ; if ( ! $result ) { die ( __line__ . "_update_r_rost_rma_" . mysql_error() ) ; } } Code: [Select] $ids = implode (",", $ibforums->input['checkbox']); $time = time(); $ids2 = implode (",", $ibforums->input['pendingusers']); $DB->query("UPDATE friends_pending SET pending='0',date=$time WHERE id IN ($ids) AND toid IN ($ids2)"); weird thing is, it's not bring up any error's or nothing $ids2 spits out 2,30 and $ids spits out 9,7 for this particular project doesn't give me mysql error or nothing, script runs fine. I have mysql error enabling under $dbquery class so nn to worry, how to get this to work? Can i even use 2 IN's in 1 query or??? I have a database called "postvoting", It's basically to store when somebody votes on a particular posts. I store the post_id that the user votes on, the users id that voted on it, and the date. What I want to do is find the most popular posts in a given time. So if 4 people voted on the post with the id of 1, and 2 people voted on the post with the id of 2, I want to count the number of rows with post_id='1' A non working example what I want would look something like: $count_votes = mysql_query("SELECT * FROM postvoting WHILE post_id=post_id"); print mysql_num_rows($count_votes); result: 4 or $count_votes = mysql_query("SELECT * FROM postvoting GROUP BY post_id"); print mysql_num_rows($count_votes); result: 4 (counting the number of results in a group) Hope this isn't too confusing. (I've confused myself with this). This topic has been moved to MySQL Help. http://www.phpfreaks.com/forums/index.php?topic=314391.0 This topic has been moved to MySQL Help. http://www.phpfreaks.com/forums/index.php?topic=350117.0 Im sure this is simple, but I cannot see what my problem is! I am hitting an error on my insert query Code: [Select] 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 '1' at line 1 I know the output of $user_id is 1, so my error is on $mysavepath Code: [Select] $mysavepath = $folder.'/'.$worldname.'_'.date("dMjY"); echo $mysavepath; $savepath = mysql_query("INSERT INTO saves (user_id,savepath) VALUES ('$user_id','$mysavepath')"); echo '<br>'.$savepath; if(!mysql_query($savepath)) { die('<br>Error: ' . mysql_error()); } however it all echos out ok? Code: [Select] 188ea678f0dcdc8252aeb15e3c910408/world_15Jan152012 1 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 '1' at line 1 Can anyone see the problem? Cheers Dave This topic has been moved to MySQL Help. http://www.phpfreaks.com/forums/index.php?topic=329660.0 The following query or while loop is only increasing the ArticleID variable every 3rd time the script is run, I've narrowed it down to the following code snippet. Can you spot a problem with this, I'm in my first week of PHP and MySQL and I can't see any problem with it. Any help would be mighty appreciated by this idiot Code snippet: --- $result = mysql_query("SELECT ArticleID FROM test_top ORDER BY ArticleID ASC LIMIT 1") or die(mysql_error()); while($row = mysql_fetch_array($result)) { $ArticleID=$row['ArticleID']; } $ArticleID=intval($ArticleID); $ArticleID++; --- Hi, very simple i'm sure... i have a code for a text box to pull info into from a mysql table. easy enough: Code: [Select] <?php echo "<input type=\"text\" id=\"b_info\" name=\"b_info\" value=\"$b_info\" style=\"width: 327px\" /text>"; ?> but suppose i dont just want a "line of text" i want one of those boxes with the scroll bar down the side (lol like the one im writing this in ) how do i change the above code to do it? i though doing this: Code: [Select] <?php echo "<input type=\"textarea\" id=\"b_info\" name=\"b_info\" value=\"$b_info\" style=\"width: 327px\" style=\"height: 185px\" /textarea>"; ?> would work, but obviously not, all ive done is make the box bigger easy workaround? Thanks Hello, I am trying to pick up php again and just exercising my skills. So I have it so that it fills my form with the values of what I want to edit, and when I click the edit button, it doesn't edit any of the information. When I echo out $result, I get a MYSQL query string that has the same values as the table, so its not getting the new values that are edited. <?php @mysql_connect('localhost', 'root', '') or die("Could not connect to Mysql Server. " . mysql_error()); @mysql_select_db('tutorials') or die("Could not connect to Database. " . mysql_error()); if(isset($_GET['edit'])) { $id = $_GET['edit']; $query = "SELECT `username`, `password` FROM `users` WHERE `id` = '$id'"; $result = mysql_query($query); $row = mysql_fetch_array($result); $name = $row['username']; $password = $row['password']; } if(isset($_POST['edit'])) { $id = $_GET['edit']; $query = "UPDATE `users` SET `username` = '$name', `password` = '$password' WHERE `id` = '$id'"; $result = mysql_query($query); echo $query; if(!$result) { echo mysql_error(); }else{ echo 'updated post'; } } ?> <form method="POST" action="" > <input type="text" name="name" value="<?php echo $name; ?>" /> First name <br /> <input type="text" name="password" value="<?php echo $password; ?>" /> Last name <br /> <input type="submit" name="edit" value="edit" /> </form> I believe it has something to do with the values of $name and $password in the form conflicting with the first if isset and the second if isset. Thanks for any help possible I'm so sorry for this question but I not really know how to play with single and double quote. If I have a query like this: Code: [Select] mysql_query('UPDATE table SET Status=1,Sending=Done WHERE ID IN ('.implode(',', $done).')'); And I wish to add Code: [Select] SentAt='$date' in the query as well , and I try this: Code: [Select] mysql_query('UPDATE table SET Status=1,Sending=Done,SentAt='$date' WHERE ID IN ('.implode(',', $done).')'); Not working...how should I write it? Thank you. I'm building a query that searches by database and returns matching (or almost matching) terms. That part isn't the problem- I have it up and working. The problem is that I'm trying to narrow down the search results, and it's not working. Here's the query that works: Code: [Select] $result = mysql_query("SELECT * FROM auctions WHERE name LIKE '%".$searchterm."%' OR Address LIKE '%".$searchterm."%' OR state like '%".$searchterm."%'"); Here's the query that DOESN'T works: Code: [Select] $result = mysql_query("SELECT * FROM auctions WHERE type='Cars' AND name LIKE '%".$searchterm."%' OR Address LIKE '%".$searchterm."%' OR state like '%".$searchterm."%'"); What I'm trying to do is say "give me all the results from type:Cars. Instead, it ignores the WHERE type='Cars' statement, and returns results for all types. It frustrates me because I use the same exact query in a thousand other places, and it works everywhere else. For example: Code: [Select] $sql = "SELECT * FROM auctions WHERE type='Boats' AND state='$v4' ORDER BY $v1 $v2"; works just fine. I'm not exactly an expert on any of this, but I can see no logical reason why this works, but the Search code doesn't. They appear in all ways identical, at least as far as query structure goes. Can anyone spot where I screwed up? Thanks! Kyle |
|||||||