A community in which webmasters can ask for help with topics such as PHP coding , MySQL , IT jobs, web design, IT security.
Current location:homephp forumphp talk in 2008 yearphp and sql query failing - page 1
User InfoPosts
php and sql query failing#1
I have this code


which is part of a small ajax application. I would like to know a better, more efficient way to assign $query, instead of copying the sql each time with a different query or a bunch of if clauses. Basically the query will be dependant on the link clicked, but I am not sure how to show that in the logic. I am also unsure why my SQL query in $result fails.

posted date: 2008-12-09 03:32:00

Re: php and sql query failing#2
I had made out the solution of this problem. click to view my topic...

hope that hepls.

posted date: 2008-12-09 03:32:01

Re: php and sql query failing#3
UPDATE: I integrated Eran's function into the refactored code. NOTE: I corrected it by passing the $table variable into it and renamed it since it doesn't search the query text only but mainly returns the needed rows!MAIN MISTAKES:mistake 1: you overwrite query with query2 in all cases which breaks the code.mistake 2: LIKE'%$query%' there is a space missing between LIKE and ' => LIKE '%... this most probably breaks your code tooOTHER ISSUESsecurity problem: sql injection danger, use mysql_real_escape_string\n not platform independent: use PHP_EOLalternative way of writing short if blocksuse curly brackets for normal if structures and all such structures for the matterhere is your code with some changes, look at the comments:<?phpsession_start(); //ommit, no session var used//use braces, always!//you may write such statements with the short form likeif (isset($_GET['cmd'])) : $cmd = $_GET['cmd']; else : die (_MSG_NO_PARAM); endif;$query = '';//escpae your input - very important for security! sql injection!if ( isset ($_GET["query"])){ $query = mysql_real_escape_string($_GET["query"]);}//no need for the other part you had here$con = mysql_connect("localhost", "root", "geheim");if (!$con) : die ('Connection failed. Error: '.mysql_error()); endif;mysql_select_db("ebay", $con);if ($cmd == "GetRecordSet"){ $table = 'Auctions'; $rows = getRowsByArticleSearch($searchString, $table); //use PHP_EOL instead of \n in order to make your script more portable echo "<h1>Table: {$table}</h1>".PHP_EOL; echo "<table border='1' width='100%'><tr>".PHP_EOL; echo "<td width='33%'>Seller ID</td>".PHP_EOL; echo "<td width='33%'>Start Date</td>".PHP_EOL; echo "<td width='33%'>Description</td>".PHP_EOL; echo "</tr>\n"; // printing table rows foreach ($rows as $row) { $pk = $row['ARTICLE_NO']; echo '<tr>'.PHP_EOL; echo '<td><a href="#" onclick="GetAuctionData(\''.$pk.'\')">'.$row['USERNAME'].'</a></td>'.PHP_EOL; echo '<td><a href="#" onclick="GetAuctionData(\''.$pk.'\')">'.$row['ACCESSSTARTS'].'</a></td>'.PHP_EOL; echo '<td><a href="#" onclick="GetAuctionData(\''.$pk.'\')">'.$row['ARTICLE_NAME'].'</a></td>'.PHP_EOL; echo '</tr>'.PHP_EOL; }}mysql_free_result($result);//mysql_close($con); no need to close connection, you better don'tfunction getRowsByArticleSearch($searchString, $table) { $searchString = mysql_real_escape_string($searchString); $result = mysql_query("SELECT ARTICLE_NO, USERNAME, ACCESSSTARTS, ARTICLE_NAME FROM {$table} WHERE upper ARTICLE_NAME LIKE '%" . $searchString . "%'"); if($result === false) { return mysql_error(); } $rows = array(); while($row = mysql_fetch_assoc($result)) { $rows[] = $row; } return $rows;}// ?> ommit closing php tag

posted date: 2008-12-09 03:44:00

Re: php and sql query failing#4
You haven't enclosed the statements in your IF/THEN/ELSE constructions in accolades so only the first statement in every block is conditionally executed, the rest allways is.In most cases you'd be assigning $query2 to $query while $query2 probably hasn't been defined.As another tip: sanitize your input, don't go pasting user input into your SQL like that, it's dangerous.

posted date: 2008-12-09 03:47:00

Re: php and sql query failing#5
"SELECT ARTICLE_NO, USERNAME, ACCESSSTARTS, ARTICLE_NAMEFROM {$table} WHERE upper ARTICLE_NAME LIKE'%$query%'"You need to put brackets around the parameters of your upper function. change your query to this, and it should work:"SELECT ARTICLE_NO, USERNAME, ACCESSSTARTS, ARTICLE_NAMEFROM {$table} WHERE upper(ARTICLE_NAME) LIKE'%$query%'"

posted date: 2008-12-09 03:48:00

Re: php and sql query failing#6
for a feature use:$result = mysql_query($sql_query) or die(mysql_error());To see what kind of mysql error you get.

posted date: 2008-12-09 03:50:00

Re: php and sql query failing#7
You may need a space between LIKE and '%$query%'. Also, you should look into the mysql_error() function - let MySQL tell you exactly what the error is.

posted date: 2008-12-09 03:51:00

Re: php and sql query failing#8
You can abstract your query in a function that accepts the search text as a parameter. Something like:function searchQuery($text) {$text = mysql_real_escape_string($text);$result = mysql_query("SELECT ARTICLE_NO, USERNAME, ACCESSSTARTS, ARTICLE_NAME FROM {$table} WHERE upper ARTICLE_NAME LIKE '%" . $text . "%'");if($result === false) {return mysql_error();}$rows = array();while($row = mysql_fetch_assoc($result)) {$rows[] = $row;}return $rows;}Note that you should escape user input to prevent SQL injection attacks (here I used mysql_real_escape_string() to do that). This function also returns the error code if the query fails, so you should check the result to see if it's an array or not: $result = searchQuery($_GET['query']); if(!is_array($result) ) { echo 'An error has occurred:' . $result; } else { //iterate over rows }Wrap your logical structures (IF/ELSE) with curly brackets {. It's better for readability and helps avoid unnecessary mistakes.

posted date: 2008-12-09 03:54:00

Re: php and sql query failing#9
I was gonna do this but ran. +1 for you

posted date: 2008-12-09 04:11:00

Re: php and sql query failing#10
No need for PHP_EOL. It's HTML. Firstly, line breaks doesnt matter. Secondly, all source viewers I've seen (except Notepad) work with just "\n".

posted date: 2008-12-09 04:46:00

Re: php and sql query failing#11
ok, it seems I have to look into that.

posted date: 2008-12-09 04:56:00

Re: php and sql query failing#12
the missing space doesn't cause any issues, when I tested it, at least.

posted date: 2008-12-09 15:28:00

select page: « 1 »
Copyright ©2008-2017 www.momige.com, all rights reserved.