webguync
-
Posts
951 -
Joined
-
Last visited
Posts posted by webguync
-
-
Hello,
I need to set a CSS class for the highest number in a MySQL column, and the second highest. So for instance I have a column named scores. I would want the <td> with highest scores to be <td.Winner> and the second highest to be <td. RunnerUp>. I know how to set the css part up, so just need help with creating a function. I did something similar to set the background of table cells based on the text data and that looked like this.
function cssfromdate($date) { $class = array('December_January' => 'January', 'March_April' => 'March'); return $class[$date]; } while($row = mysql_fetch_array($result)) { echo "<tr>"; echo "<td>" . $row['last_name'] . "</td>\n"; echo "<td>" . ucwords($row['first_name']) . "</td>\n"; echo "<td class=\"".cssfromdate($row['Class_Date'])."\">".$row['Class_Date']."</td>\n"; echo "</tr>"; }I am hoping I can accomplish what I need to doing something similar.
-
ok, Duh, thanks for catching that. Forgot to update my Query. Everything appears to be working now
-
I changed the SQL but still get the same error. Please explain what I am still doing wrong. SQL code below.
CREATE TABLE `Phase1A_1B_TotalScores_2011_1` ( `last_name` text, `first_name` text, `employee_id` varchar(5) default NULL, `title` varchar(10) default NULL, `territory` varchar(255) default NULL, `district` varchar(255) default NULL, `Phase1A_Score` varchar(255) default NULL, `Phase1B_Score` varchar(255) default NULL, `Phase1_Average` varchar(255) default NULL, `Phase1A_HS_Exam` varchar(255) default NULL, `Phase1A_HS_Exam_RT` varchar(255) NOT NULL, `Phase1B_HS_Exam` varchar(255) NOT NULL, `Phase1B_HS_Exam_RT` varchar(255) NOT NULL, `Class_Date` varchar(255) NOT NULL, `roster_id` int(11) NOT NULL auto_increment, PRIMARY KEY (`roster_id`), FULLTEXT KEY (`last_name`,`first_name`,`employee_id`,`title`,`territory`,`district`,`Phase1A_Score`,`Phase1B_Score`,`Phase1_Average`,`Phase1A_HS_Exam`,`Phase1A_HS_Exam_RT`,`Phase1B_HS_Exam`,`Phase1B_HS_Exam_RT`,`Class_Date`) ) ENGINE=MyISAM AUTO_INCREMENT=289 DEFAULT CHARSET=utf8 AUTO_INCREMENT=289 ;
-
I don't see where I am mixing FULLTEXT indexes. Can you explain that more and post some SQL code to index N columns?
thanks for your help!
-
hmm, I edited my SQL and took out the duplicated and tried again. Still get the same error. It looks to me like everything matches up?
CREATE TABLE `Phase1A_1B_TotalScores_2011_1` ( `last_name` text, `first_name` text, `employee_id` varchar(5) default NULL, `title` varchar(10) default NULL, `territory` varchar(255) default NULL, `district` varchar(255) default NULL, `Phase1A_Score` varchar(255) default NULL, `Phase1B_Score` varchar(255) default NULL, `Phase1_Average` varchar(255) default NULL, `Phase1A_HS_Exam` varchar(255) default NULL, `Phase1A_HS_Exam_RT` varchar(255) NOT NULL, `Phase1B_HS_Exam` varchar(255) NOT NULL, `Phase1B_HS_Exam_RT` varchar(255) NOT NULL, `Class_Date` varchar(255) NOT NULL, `roster_id` int(11) NOT NULL auto_increment, PRIMARY KEY (`roster_id`), FULLTEXT KEY `last_name` (`last_name`), FULLTEXT KEY `first_name` (`first_name`), FULLTEXT KEY `employee_id` (`employee_id`), FULLTEXT KEY `title` (`title`), FULLTEXT KEY `territory` (`territory`), FULLTEXT KEY `district` (`district`), FULLTEXT KEY `Phase1A_Score` (`Phase1A_Score`), FULLTEXT KEY `Phase1B_Score` (`Phase1B_Score`), FULLTEXT KEY `Phase1_Average` (`Phase1_Average`), FULLTEXT KEY `Phase1A_HS_Exam` (`Phase1A_HS_Exam`), FULLTEXT KEY `Phase1A_HS_Exam_RT` (`Phase1A_HS_Exam_RT`), FULLTEXT KEY `Phase1B_HS_Exam` (`Phase1B_HS_Exam`), FULLTEXT KEY `Phase1B_HS_Exam_RT` (`Phase1B_HS_Exam_RT`), FULLTEXT KEY `Class_Date` (`Class_Date`) ) ENGINE=MyISAM AUTO_INCREMENT=289 DEFAULT CHARSET=utf8 AUTO_INCREMENT=289 ;
-
I think I might see the problem, it looks like some of the fields were made FULLTEXT twice or three times, creating some unwanted duplicates, so I will need to undo some of the FULLTEXT. How do I do that?
SQL below
CREATE TABLE `Phase1A_1B_TotalScores_2011` ( `last_name` text, `first_name` text, `employee_id` varchar(5) default NULL, `title` varchar(10) default NULL, `territory` varchar(255) default NULL, `district` varchar(255) default NULL, `Phase1A_Score` varchar(255) default NULL, `Phase1B_Score` varchar(255) default NULL, `Phase1_Average` varchar(255) default NULL, `Phase1A_HS_Exam` varchar(255) default NULL, `Phase1A_HS_Exam_RT` varchar(255) NOT NULL, `Phase1B_HS_Exam` varchar(255) NOT NULL, `Phase1B_HS_Exam_RT` varchar(255) NOT NULL, `Class_Date` varchar(255) NOT NULL, `roster_id` int(11) NOT NULL auto_increment, PRIMARY KEY (`roster_id`), FULLTEXT KEY `last_name` (`last_name`), FULLTEXT KEY `SearchMe` (`last_name`,`first_name`,`employee_id`,`title`,`territory`,`district`,`Phase1A_Score`,`Phase1B_Score`,`Phase1_Average`,`Class_Date`), FULLTEXT KEY `Phase1A_HS_Exam` (`Phase1A_HS_Exam`), FULLTEXT KEY `last_name_2` (`last_name`), FULLTEXT KEY `first_name` (`first_name`), FULLTEXT KEY `employee_id` (`employee_id`), FULLTEXT KEY `title` (`title`), FULLTEXT KEY `territory` (`territory`), FULLTEXT KEY `district` (`district`), FULLTEXT KEY `Phase1A_Score` (`Phase1A_Score`), FULLTEXT KEY `Phase1B_Score` (`Phase1B_Score`), FULLTEXT KEY `Phase1_Average` (`Phase1_Average`), FULLTEXT KEY `Class_Date` (`Class_Date`), FULLTEXT KEY `district_2` (`district`), FULLTEXT KEY `territory_2` (`territory`), FULLTEXT KEY `title_2` (`title`), FULLTEXT KEY `employee_id_2` (`employee_id`), FULLTEXT KEY `Phase1A_HS_Exam_2` (`Phase1A_HS_Exam`), FULLTEXT KEY `Phase1_Average_2` (`Phase1_Average`), FULLTEXT KEY `Class_Date_2` (`Class_Date`), FULLTEXT KEY `Phase1B_HS_Exam` (`Phase1B_HS_Exam`), FULLTEXT KEY `Phase1_Average_3` (`Phase1_Average`) ) ENGINE=MyISAM AUTO_INCREMENT=289 DEFAULT CHARSET=utf8 AUTO_INCREMENT=289 ;
-
I believe it does. Here is the PHP code
$result=$db->query("SELECT last_name, first_name,employee_id,title,territory,district,Phase1A_Score,Phase1B_Score,Phase1_Average,Class_Date FROM Phase1A_1B_TotalScores_2011 WHERE MATCH(last_name, first_name,employee_id,title,territory,district,Phase1A_Score,Phase1B_Score,Phase1_Average,Class_Date) AGAINST ('$searchterm')"); -
well I found out how to get a list of the columns indexed which is with this SQL
select group_concat(distinct column_name) from information_schema.STATISTICS where table_schema = 'your_db' and table_name = 'your_table' and index_type = 'FULLTEXT';
I am still frustrated though, because all of my columns show as being FULLTEXT, but I can get the SQL error.
-
I have a search application which searches info in a MySQL table. It was working, but then I added some more columns and now get the following error in the SQL.
"#1191 - Can't find FULLTEXT index matching the column list ".
I thought I had made all of the columns FULLTEXT Anyway to show which columns aren't fulltext?
-
Here is my situation. I have an exam application built in Flash, where after the taker is finished and submits their scores a record is created in MySQL in a log table. Basically there is a column called attempt which is populated with a 1 after they have successfully finished the exam. When this column has a 1, the user is locked out and cannot access the exam when they try and login. The Flash part was built a while back in AS3 and is very cumbersome to try and figure out and make changes, so I have been trying to improve some things on the PHP/MySQL end. What I have now is a php based login which uses if else statements to determine if a user has passed and if they have not uses a DELETE statement to delete their log record and thus they regain access to taking the exam. Code below.
<?php if(($pcnt[$i]*100) > 89) { echo "<span class='green'>you did great!</span>"; } else { $query_delete = "DELETE FROM log_March2011 USING log_March2011 INNER JOIN roster_March2011 WHERE log_March2011.user_id =roster_March2011.user_id AND roster_March2011.user_id = '{$_SESSION['user_id']}'"; //echo $query_delete; //for debugging test $result_delete = mysql_query($query_delete) or trigger_error('Query failed: ' .mysql_error()); //$num = mysql_affected_rows($db); if ($result_delete) { echo "<span class='red'>Please review missed questions/sections below</span><br />You can now retake the exam"; }// end if else { echo "No record of taking exam"; } //end else } ?>what I would like to do to improve the application is to be able to implement a set number of attempts allowed before the user is disallowed to take the exam again, but not sure how to implement this or is even possible?
-
it was a matter of indexing columns that I inadvertently didn't included in my Select list.
SELECT col1, col2,col3,col4,col5 FROM ResultsTable WHERE MATCH(col1, col2,col3,col4,col5) AGAINST ('SearchName')
when I added all of the columns, I no longer got an error.
-
it looks like I figured out my own problem, thanks.
-
it's the names of the columns in my table. I was just using dummy names instead of the real ones (last_name, first_name etc.)
-
Hi,
I am working on a form that will bring up data from the MySQL tables. I did some research and realized I needed to a mult-column full-text index in order for the search application to work. In PHP MyAdmin under the SQL area I added this statement.
CREATE FULLTEXT INDEX SearchMe ON ResultsTable (col1,col2, col3.col4,col5);
I tried the search application again and get this error.
SELECT col1, col2,col3,col4,col5 FROM ResultsTable WHERE MATCH(col1, col2,col3,col4,col5) AGAINST ('SearchName')When I enter the SQL code in PHPMyAdmin the result says
"#1191 - Can't find FULLTEXT index matching the column list "
so I need to figure out which columns got indexed and which are not or why they don't match up. How can I do this?
-
makes sense, thanks. I was able to diagnose the problem.
-
I am trying to adapt a database search form from a tutorial I saw, and it's not working. I just get a blank white slate despite having
error_reporting(E_ALL);
at the top of the page. I was hoping someone might be able to spot anything obvious I might be missing.
Here is my code
<?php error_reporting(E_ALL); // include MySQL-processing classes require_once 'mysql.php'; try{ // connect to MySQL $db=new MySQL(array ('host'=>'localhost','user'=>'username','password'=>'password', 'database'=>'DBName')); $searchterm=$db->escapeString($_GET['searchterm']); $result=$db->query("SELECT last_name, first_name,employee_id,title,territory,districts FROM Scores WHERE MATCH(last_name,first_name,employee_id,title,territory,districts) AGAINST ('$searchterm')"); if(!$result->countRows()){ echo '<div class="maincontainer"><h2>No results were found. Go back and try a new search.</h2></div>'."n"; } else{ // display search results echo '<div class="maincontainer"><h2>Your search criteria returned '.$result->countRows().' results.</h2>'."n"; while($row=$result->fetchRow()){ echo '<div class="rowcontainer"><p><strong>First Name: </strong>'.$row['first_name'].'<p><p><strong>Last Name: </strong>'.$row['last_name'].'</p><p><strong>Employee ID: </strong>'.$row['employee_id'].'</p> <p><strong>Title: </strong>'.$row['title'].'</p> <p><strong>Territory: </strong>'.$row['territory'].'</p> <p><strong>Districts: </strong>'.$row['districts'].'</p> </div>'."n"; } } echo '</div>'; } catch(Exception $e){ echo $e->getMessage(); exit(); } ?>and here is the included file mysql.php code
<?php // define 'MySQL' class class MySQL{ private $conId; private $host; private $user; private $password; private $database; private $result; const OPTIONS=4; public function __construct($options=array()){ if(count($options)!=self::OPTIONS){ throw new Exception('Invalid number of connection parameters'); } foreach($options as $parameter=>$value){ if(!$value){ throw new Exception('Invalid parameter '.$parameter); } $this->{$parameter}=$value; } $this->connectDB(); } // connect to MySQL private function connectDB(){ if(!$this->conId=mysql_connect($this->host,$this->user,$this- >password)){ throw new Exception('Error connecting to the server'); } if(!mysql_select_db($this->database,$this->conId)){ throw new Exception('Error selecting database'); } } // run query public function query($query){ if(!$this->result=mysql_query($query,$this->conId)){ throw new Exception('Error performing query '.$query); } return new Result($this,$this->result); } public function escapeString($value){ return mysql_escape_string($value); } } // define 'Result' class class Result { private $mysql; private $result; public function __construct(&$mysql,$result){ $this->mysql=&$mysql; $this->result=$result; } // fetch row public function fetchRow(){ return mysql_fetch_assoc($this->result); } // count rows public function countRows(){ if(!$rows=mysql_num_rows($this->result)){ return false; } return $rows; } // count affected rows public function countAffectedRows(){ if(!$rows=mysql_affected_rows($this->mysql->conId)){ throw new Exception('Error counting affected rows'); } return $rows; } // get ID form last-inserted row public function getInsertID(){ if(!$id=mysql_insert_id($this->mysql->conId)){ throw new Exception('Error getting ID'); } return $id; } // seek row public function seekRow($row=0){ if(!is_int($row)||$row<0){ throw new Exception('Invalid result set offset'); } if(!mysql_data_seek($this->result,$row)){ throw new Exception('Error seeking data'); } } } ?> -
never mind. I had error reporting set to ALL. I just took that out and no more notice so all is good. Thanks again!
-
thanks, seems to be working as intended now. I am still getting this notice though.
Notice: Undefined index: in index.php on line 57
which is this line
[php
return $class[$date];
[/code]how do I get rid of that notice?
-
I am getting some notices with the code I am trying
Notice: Undefined offset: 1 in index.php on line 57 Notice: Undefined index: in index.php on line 62
I changed the database content to be either 'January' or 'March' just to try this out.
in my PHP
function cssfromdate($date) { $parts = explode('-',$date); $month = $parts[1]; $class = array('January' => 'January', 'March' => 'March', ); return $class[$month]; } while($row = mysql_fetch_array($result)) { echo "<tr>"; echo "<td>" . $row['last_name'] . "</td>\n"; echo "<td>" . ucwords($row['first_name']) . "</td>\n"; echo "<td class=\"".cssfromdate($row['Class_Date'])."\">".$row['Class_Date']."</td>\n"; echo "</tr>"; }it looks like the notices are for these two lines
$month = $parts[1]; return $class[$month];
please explain what I need to do to fix.
-
ok neat thanks. So if I wanted to change the array keys it would be...
$class = array('January' => 'red', 'February' => 'green', 'March' => 'blue', 'April' => 'orange', etc... '); return $class[$month]; }?
-
currently it is just text (January 2011), but I can change to any format it needs to be.
-
I am trying to figure out the best way to do something if there is a way.
I am pulling values from a MySQL table and using a while loop to display the data, so it looks something like this.
while($row = mysql_fetch_array($result)) { echo "<tr>"; echo "<td>" . $row['last_name'] . "</td>\n"; echo "<td>" . ucwords($row['first_name']) . "</td>\n"; echo "<td>" . $row['Class_Date'] . "</td>\n"; echo "</tr>";for the class dates, I want the column background to be a different color depending on the date. For instance January would hvae a blue background, February a red one etc.
I was going to try and do that with CSS like
echo "<td class="blue">" . $row['Class_Date'] . "</td>\n";
in the CSS...
.blue{ background-color:#06c; }but not sure the best way to make the changing data values different colors with CSS.
any suggestions?
-
I have a script that is supposed to submit my form using process.php and deliver a success or failure message on the form page. The form does submit, but the JQuery portion is working. I just get a generic message from my process.php page that the form was submitted. I am not sure what is amiss, so was hoping someone might have some suggestions.
Here is the JQuery code.
<script type="text/javascript" src="http://code.jquery.com/jquery-latest.js"></script> <script type="text/javascript"> var J = jQuery.noConflict(); J(document).ready(function() { //if submit button is clicked J('#submit').click(function () { //Get the data from all the fields var name = J('input[name=name]'); var email = J('input[name=email]'); var website = J('input[name=website]'); var comment = J('textarea[name=comment]'); //Simple validation to make sure user entered something //If error found, add hightlight class to the text field if (name.val()=='') { name.addClass('hightlight'); return false; } else name.removeClass('hightlight'); if (email.val()=='') { email.addClass('hightlight'); return false; } else email.removeClass('hightlight'); if (comment.val()=='') { comment.addClass('hightlight'); return false; } else comment.removeClass('hightlight'); //organize the data properly var data = 'name=' + name.val() + '&email=' + email.val() + '&website=' + website.val() + '&comment=' + encodeURIComponent(comment.val()); //disabled all the text fields J('.text').attr('disabled','true'); //show the loading sign J('.loading').show(); //start the ajax J.ajax({ //this is the php file that processes the data and send mail url: "process.php", //GET method is used type: "GET", //pass the data data: data, //Do not cache the page cache: false, //success success: function (html) { //if process.php returned 1/true (send mail success) if (html==1) { //hide the form J('.form').fadeOut('slow'); //show the success message J('.done').fadeIn('slow'); //if process.php returned 0/false (send mail failed) } else alert('Sorry, unexpected error. Please try again later.'); } }); //cancel the submit button default behaviours return false; }); }); </script>the form code...
<!-- Start HTML form --> <form id="form" action="process.php" method="post" name="ContactForm"> <label for="Name">Name</label> <input type="text" name="name" id="name" /> <label for="email">E-mail</label> <input type="text" name="email" id="email" /> <label for="website">Website (example: http://www.yourwebsite.com) </label> <input type="text" name="website" id="website" /> <label for="message">Message (resize the textarea if needed)</label> <textarea class="resizable" name="comment"> </textarea> <input type="submit" name="submit" id="submit" value="Submit"> </form>
and the process.php code
<?php //Retrieve form data. //GET - user submitted data using AJAX //POST - in case user does not support javascript, we'll use POST instead $name = ($_GET['name']) ? $_GET['name'] : $_POST['name']; $email = ($_GET['email']) ?$_GET['email'] : $_POST['email']; $website = ($_GET['website']) ?$_GET['website'] : $_POST['website']; $comment = ($_GET['comment']) ?$_GET['comment'] : $_POST['comment']; //flag to indicate which method it uses. If POST set it to 1 if ($_POST) $post=1; //Simple server side validation for POST data, of course, you should validate the email if (!$name) $errors[count($errors)] = 'Please enter your name.'; if (!$email) $errors[count($errors)] = 'Please enter your email.'; if (!$comment) $errors[count($errors)] = 'Please enter your comment.'; //if the errors array is empty, send the mail if (!$errors) { //recipient $to = 'My Name <email@gmail.com>'; //sender $from = $name . ' <' . $email . '>'; //subject and the html message $subject = 'Comment from ' . $name; $message = ' <!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></head> <body> <table> <tr><td>Name</td><td>' . $name . '</td></tr> <tr><td>Email</td><td>' . $email . '</td></tr> <tr><td>Website</td><td>' . $website . '</td></tr> <tr><td>Comment</td><td>' . nl2br($comment) . '</td></tr> </table> </body> </html>'; //send the mail $result = sendmail($to, $subject, $message, $from); //if POST was used, display the message straight away if ($_POST) { if ($result) echo 'Thank you! I have received your message.'; else echo 'Sorry, unexpected error. Please try again later'; //else if GET was used, return the boolean value so that //ajax script can react accordingly //1 means success, 0 means failed } else { echo $result; } //if the errors array has values } else { //display the errors message for ($i=0; $i<count($errors); $i++) echo $errors[$i] . '<br/>'; echo '<a href="form.php">Back</a>'; exit; } //Simple mail function with HTML header function sendmail($to, $subject, $message, $from) { $headers = "MIME-Version: 1.0" . "\r\n"; $headers .= "Content-type:text/html;charset=iso-8859-1" . "\r\n"; $headers .= 'From: ' . $from . "\r\n"; $result = mail($to,$subject,$message,$headers); if ($result) {return 1;} else {return 0;} } ?>again, the form submits fine, so there is no problem there, but the JQuery is being bypassed.
-
thanks, I think it is a php setting too. I will check on that.
form to search DB content not working correctly
in Applications
Posted
I am trying out some code I got from a tutorial and it's not working for me. The code is for a form and when you type in any letter or string that matches against the DB content it is supposed to display. I only get the 'No Matches Found ' string as a result. No DB errors or anything. Also I echoed out the resulting SQL and when I enter that into PHPMyAdmin I come up with results.
I am hoping it's just something obvious i am overlooking. Also I am using JQuery and AJAX, but this isn't causing it not to work b/c I tested without the JQuery and still get the same result.
code posted below
<html> <head> <script language="javascript" type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.6.0/jquery.min.js" /> </script> <script type='text/javascript'> $(document).ready(function(){ $("#search_results").slideUp(); $("#search_button").click(function(e){ e.preventDefault(); ajax_search(); }); $("#search_term").keyup(function(e){ e.preventDefault(); ajax_search(); }); }); function ajax_search(){ $("#search_results").show(); var search_val=$("#search_term").val(); $.post("./find.php", {search_term : search_val}, function(data){ if (data.length>0){ $("#search_results").html(data); } }) } </script> <meta http-equiv="Content-Type" content="text/html; charset=iso- 8859-1" /> <title>Novo RPC Results Search Engine</title> <link href="default.css" rel="stylesheet" type="text/css" media="screen" /> </head> <body> <h1>Search our Phone Directory</h1> <form id="searchform" method="post" action="find.php"> <div> <label for="search_term">Search name/phone</label> <input type="text" name="search_term" id="search_term" /> <input type="submit" value="search" id="search_button" /> </div> </form> <div id="search_results"></div> </body> </html>the PHP stuff
<?php define(HOST, "localhost"); define(USER, "username"); define(PW, "pw"); define(DB, "DB_Name"); $connect = mysql_connect(HOST,USER,PW) or die('Could not connect to mysql server.' ); mysql_select_db(DB, $connect) or die('Could not select database.'); $term = strip_tags(substr($_POST['search_term'],0, 100)); $term = mysql_escape_string($term); $sql = "select name,phone from directory where name like '%$term%' or phone like '%$term%' order by name asc"; $string = ''; if (mysql_num_rows($result) > 0){ while($row = mysql_fetch_object($result)){ $string .= "<b>".$row->name."</b> - "; $string .= $row->phone."</a>"; $string .= "<br/>\n"; } }else{ $string = "No matches found!"; } echo $string; echo $sql; ?>and the DB code