|
PHP - Mysql Query Executes In Phpmyadmin But Returns Nothing Using Php Script
Hi,
I have a sql query that executes perfectly in phpMyAdmin but when applied to a PHP script returns nothing. It won't even start a "while" loop. Please review code snippits below. Submission Script calls function in Database Class: $result = $database->testReturnId($_POST['years'], $_POST['model']); while($row = mysql_fetch_array($result)) { $i = $i + 1; echo "ROW " . $i . " " . $row['auto_id'] . " " . $row['auto_year'] . " " . $row['auto_year_high'] . " " . $row['auto_make'] . " " . $row['auto_model'] . "<br />"; } Database Class Function: function testReturnId($year, $model){ global $form; $q = 'SELECT * FROM tbl_svc_auto WHERE auto_model = "$model" AND "$year" BETWEEN auto_year AND auto_year_high'; if(!mysql_query($q, $this->connection)){ return false; }else{ return $result = mysql_query($q, $this->connection); } } I have moved the query to the submission script and replaced the variables with actual data and it still will not work. I can take the same query with out the variables and input it into phpMyAdmin and it works great. On the submission script, I added a row number to the echo thinking that it would a least return the word "ROW" and it does not. I have tested each step of the execution of the query and have not returned any errors. I do not receive any errors at all even during execution. It just seems to refuse to run the "while" statement. I do not know where my problem is......Please help. Thank you in advance for any assitance you can offer. Joshua Similar TutorialsI have a really weird problem. I'm trying to run a mysql query that works fine in phpmyadmin but in php script is giving me an error. The query is: Code: [Select] (SELECT DISTINCT art.`TEMPLATE`,gal.`ARTICLE_ID`,art.`TITLE`,art.`DESCRIPTION`,MATCH(art.`TITLE`,art.`DESCRIPTION`,gal.`CONTENT`) AGAINST ('WORD*' IN BOOLEAN MODE) AS score FROM articles art,galeries gal WHERE gal.`ARTICLE_ID`=art.`ARTICLE_ID` AND MATCH(art.`TITLE`,art.`DESCRIPTION`,gal.`CONTENT`) AGAINST ('WORD* ' IN BOOLEAN MODE)) UNION (SELECT DISTINCT `TEMPLATE`,`ARTICLE_ID`,`TITLE`,`DESCRIPTION`,MATCH(`TITLE`,`DESCRIPTION`,`CONTENT`) AGAINST ('WORD* ' IN BOOLEAN MODE) AS score FROM articles WHERE (MATCH(`TITLE`,`DESCRIPTION`,`CONTENT`) AGAINST ('WORD* ' IN BOOLEAN MODE))) ORDER BY score DESC LIMIT 0,30 Snipset from php script code: function search($start_row,$ammount,$search_key,$pages){ $start_row = intval($start_row) * $ammount; $return_val = ""; $symbols = array('/','\\','\'','"',',','.','<','>','?',';',':','[',']','{','}','|','=','+','-','_',')','(','*','&','^','%','$','#','@','!','~','`' );//this will remove punctuation $pattern = "#[^(\w|α|β|γ|δ|ε|ζ|η|θ|ι|κ|λ|μ|ν|ξ|ο|π|ρ|σ|τ|υ|φ|χ|ψ|ω|Α|Β|Γ|Δ|Ε|Ζ|Η|Θ|Ι|Κ|Λ|Μ|Ν|Ξ|Ο|Π|Ρ|Σ|Τ|Υ|Φ|Χ|Ψ|Ω|ς|ά|έ|ό|ί|ύ|ώ|ή|ϊ|ϋ|ΐ|ΰ|Ά|Έ|Ό|Ί|Ύ|Ώ|Ή|Ϊ|Ϋ|\d|\'|\"|\.|\!|\?|;|,|\\|\/|\-|:|\&|@)]+#"; $search_key = greek_text::to_upper($search_key); $wc = strip_tags($search_key); $wc = preg_replace($pattern, " ", $wc); for ($i = 0; $i < sizeof($symbols); $i++) { $wc = str_replace($symbols[$i],' ',$wc); } $wc = str_replace("΄", " ", $wc); $wc = str_replace(chr(162), " ", $wc); if( !$keep_numbers ) { $wc = preg_replace('#(^|\s+)[\d\s]+(\s+|$)#',' ',$wc); $pattern = '#(^|\s+)([0-9]+[a-zA-ZαβγδεζηθικλμνξοπρστυφχψωΑΒΓΔΕΖΗΘΙΚΛΜΝΞΟΠΡΣΤΥΦΧΨΩςάέόίύώήϊϋΐΰΆΈΌΊΎΏΉΪΫ]+\s*)+(\s+|$)#'; preg_match($pattern,$wc,$aa); $wc = preg_replace($pattern, " ", $wc); } $wc = trim(preg_replace("/\s\s+/", " ", $wc)); $wc = explode(" ", $wc); $cleaned_keyword = array_filter($wc); $cleaned_keyword = greek_text::removeStopWordsFromArray($cleaned_keyword); $stemmed_keywords = greek_text::stemWordsArray($cleaned_keyword); $query = "(SELECT DISTINCT art.`TEMPLATE`,gal.`ARTICLE_ID`,art.`TITLE`,art.`DESCRIPTION`,MATCH(art.`TITLE`,art.`DESCRIPTION`,gal.`CONTENT`) AGAINST ('"; while(list($key,$val)=each($stemmed_keywords)){ if($val<>" " and strlen($val) > 1){ $query .= $val."*"; $search_keys .= $val."* "; } } $query .= "' IN BOOLEAN MODE) AS score FROM articles art,galeries gal WHERE gal.`ARTICLE_ID`=art.`ARTICLE_ID` AND MATCH(art.`TITLE`,art.`DESCRIPTION`,gal.`CONTENT`) AGAINST ('".$search_keys."' IN BOOLEAN MODE))"; $query .= " UNION (SELECT DISTINCT `TEMPLATE`,`ARTICLE_ID`,`TITLE`,`DESCRIPTION`,MATCH(`TITLE`,`DESCRIPTION`,`CONTENT`) AGAINST ('".$search_keys."' IN BOOLEAN MODE) AS score FROM articles WHERE (MATCH(`TITLE`,`DESCRIPTION`,`CONTENT`) AGAINST ('".$search_keys."' IN BOOLEAN MODE))) ORDER BY score DESC LIMIT ".intval($start_row).",".$ammount; $rs = $this->dbActions->execQuery($query); $this->dbActions->execQuery("INSERT INTO searches (`KEY`,`DATE`,`RESULTS`) VALUES ('".$search_key."',NOW(),".$pages.")"); $search_results = "<div id='results'>"; while($row = mysql_fetch_array($rs)){ $search_results.= "<div id='result'>"; $search_results.= "<div class='result_title'><h4><a href='article.php?articleId=".$row["ARTICLE_ID"]."'>".$row['TITLE']."</a></h4></div>"; $search_results.= "<div class='result_description'>".$row['DESCRIPTION']."</div>"; $search_results.= "</div>"; $search_results.= "<div class='result_seperator'></div>"; } $search_results .= "</div>"; $return_val = $search_results; return $return_val; } dbactions class: require 'includes/errors.php'; error_reporting(0); class DBActions{ var $dbCon; var $errorHandler; function DBActions(){ $this->dbCon = $this->dbCon(); $errorHandler = new errors(1); } private function dbCon(){ require 'conf/configuration.php'; $dbcon = mysql_connect($dbUrl,$dbUser,$dbPass); if(!$dbcon)trigger_error("Unable to connect to database $dbUrl for user $dbUser",E_USER_ERROR); mysql_select_db($dbName); mysql_query("SET NAMES 'UTF8'"); mysql_query('set character set utf8'); return $dbcon; } function dbClose(){ if($this->dbCon) mysql_close($this->dbCon); } function execQuery($query){ $result = mysql_query($query,$this->dbCon); $msg = "Unable to execute query ".$query; if(mysql_num_rows($result) > 0) return $result; else if($result == false) trigger_error($msg,E_USER_ERROR); else return $result; } function send_error_mail(){ $this->errorHandler->sendErrorEmail(); } } I've try everything but can't get this working.. Any healp whould be really appreciated. Thanks in advance. Good Afternoon Team, Am sitting with something simple using the language below. If I copy the echo output of my query as included below it works perfectly in phpmyadmin but doesn't work on a website. Variables all seem to echo consistently/correctly and POST checks seem to verify this is working correctly as well. I worry the error comes with the syntax I used in combining the sql queries. That, or perhaps LAST_INSERT_ID does not work in the php script as well as it does in phpmyadmin. All help appreciated.
if(isset($_POST[`region_id`])) {
Server version: 5.1.53-log I have the following queries Code: [Select] SELECT user_id FROM phpbb_profile_fields_data WHERE pf_rsname = "Atroxide" LIMIT 1 SELECT user_id FROM phpbb_profile_fields_data WHERE pf_rsname = "Delia Smith" LIMIT 1 SELECT user_id FROM phpbb_profile_fields_data WHERE pf_rsname = "espinozagabe" LIMIT 1 SELECT user_id FROM phpbb_profile_fields_data WHERE pf_rsname = "Jaunty1" LIMIT 1 SELECT user_id FROM phpbb_profile_fields_data WHERE pf_rsname = "lvoos" LIMIT 1 All 5 of these queries are executed at a different time (in a foreach loop). All 5 except for the one below returned a result. Code: [Select] SELECT user_id FROM phpbb_profile_fields_data WHERE pf_rsname = "Delia Smith" LIMIT 1 I couldn't figure out why it wasn't working so I copy pasted it into PHPMyAdmin and it returned the result I was looking for. What could cause for PHPMyAdmin to work but not the exact same query in a php script to not? It didn't return an error using mysql_error() either. Pretty sure its irreverent but here is the php script. Code: [Select] foreach ($online as $username => $activity) { $query = " SELECT user_id FROM phpbb_profile_fields_data WHERE pf_rsname = \"" . $username . "\" LIMIT 1 "; $result = $db->query($query); } The table is Code: [Select] user_id mediumint(8) UNSIGNED No 0 pf_rsname varchar(255) utf8_bin Yes NULL
Hi, this query runs fine when I run it from PHPMyAdmin: UPDATE `tran_term_taxonomy` SET `description` = (SELECT keyword from `good_keywords` ORDER BY RAND() LIMIT 1,1) WHERE `tran_term_taxonomy`.`taxonomy` = 'post_tag' AND `tran_term_taxonomy`.`description` = "" LIMIT 1 However, when I run the same query in a PHP file on my server, the page doesn't load at all. The message I get is: www.somesite.com is currently unable to handle this request. HTTP ERROR 500. This is my PHP code:
<?php
include("/database/connection/path/db_connect.php");
$result4 = mysqli_query($GLOBALS["___mysqli_ston"], "UPDATE `tran_term_taxonomy` SET `description` = (SELECT keyword from `good_keywords` ORDER BY RAND() LIMIT 1,1) WHERE `tran_term_taxonomy`.`taxonomy` = 'post_tag' AND `tran_term_taxonomy`.`description` = "" LIMIT 1");
echo $result4;
?>
So how do I make this query work please? Thanks for your guidance. soo i get info from a form and i want to search for it in my db .. if i find user_name AND user_password the $result returns true .. otherwise false .. <?php require_once("../includes/connection.php"); ?> <?php $user_name=$_POST["user_name"]; $user_password=$_POST["user_password"]; ?> <?php $query = "SELECT * FROM users WHERE '{$user_name}' = user_name AND '{$user_password}'= user_password "; $result = mysql_query($query,$connection); if($result==false){ redirect("../index.php");} elseif($result==true){ redirect("../welcome.php"); } ?> <?php //close connection! mysql_close($connection); ?> but the problem is that it always returns TRUE !!!! whats the problem here exactly ??? Good evening, I am currently doing a web application which requires pulling out of data from the database. I am still a novice in the programming industry, and is still seeking help from my colleagues and of course forum sites like phpdn. I have an existing code which my friend provided me. I have already done some modifications with the code. I have a problem though with the code, it executes database query upon loading of the page. I do understand how the code works, however, I am not able to modify the code to disallow the execution of query upon loading of page. Here is the code: <?PHP include("dbconnection.php"); $query = "SELECT * FROM records"; if(isset($_POST["btnSearch"])) { $query .= " WHERE last_name LIKE '%".$_POST["search"]."%' OR first_name LIKE '%".$_POST["search"]."%'OR territory LIKE '%".$_POST["search"]."%'OR job_title LIKE '%".$_POST["search"]."%'OR title LIKE '%".$_POST["search"]."%'OR employer LIKE '%".$_POST["search"]."%' " ; } $result = mysql_query($query, $connection) or die(mysql_error()); ?> I do know that this php code, the way it is written, is supposed to do that - to select data from my database (This code was provided by a friend). But my requirement for the project is actually to give it a search engine and display the information based from the search query. I have a search engine already together with the code, and it works pretty well. What I must do is to disallow the pulling of data from the first load, but just pull data if the search engine is used. Here's the whole code: <link href="add_client.css" rel="stylesheet" type="text/css"> <?PHP include("dbconnection.php"); $query = "SELECT * FROM records"; if(isset($_POST["btnSearch"])) { $query .= " WHERE last_name LIKE '%".$_POST["search"]."%' OR first_name LIKE '%".$_POST["search"]."%'OR territory LIKE '%".$_POST["search"]."%'OR job_title LIKE '%".$_POST["search"]."%'OR title LIKE '%".$_POST["search"]."%'OR employer LIKE '%".$_POST["search"]."%' " ; } $result = mysql_query($query, $connection) or die(mysql_error()); ?> <table width="760" border="0" align="center" cellpadding="0" cellspacing="0"> <tr> <td><table width="760" border="0" cellpadding="0" cellspacing="0"> <tr> <td width="199" align="center" valign="top"><a href="login.html"><img src="asia.gif" alt="" width="152" height="58" border="0" /></a> <script type="text/javascript" src="menu.js"></script></td> <td width="176" align="right" valign="bottom"><a href="main.php"><img src="Home.jpg" width="104" height="20" border="0"/></a></td> <td width="130" align="right" valign="bottom"><img src="View.jpg" width="104" height="20" border="0"/></td> <td width="146" align="right" valign="bottom"><a href="add_client.php"><img src="Add.jpg" width="104" height="20" border="0"/></a></td> <td width="109" align="right" valign="bottom"> </td> </tr> </table></td> </tr> <tr> <td><table width="760" border="0" cellpadding="0" cellspacing="0"> <tr> <td width="200" height="3" bgcolor="#1B1C78"><img src="images/topspacerblue.gif" alt="" width="1" height="3" /></td> <td width="560" bgcolor="#0076CC"><img src="images/topspacerlblue.gif" alt="" width="1" height="3" /></td> </tr> </table></td> </tr> <tr> <td height="553" align="center" valign="top" bgcolor="#F3FAFE"><br /> <form name="form" action="view_client.php" method="post"> <table width="351" border="0"> <tr> <td width="137" align="left" valign="middle">SEARCH RECORD:</td> <td width="144" align="center" valign="middle"><input type="text" name="search" /></td> <td width="56" align="left" valign="middle"><input type="submit" name="btnSearch" value="Search" /></td> </tr> </table> <br /> <table width="680" border="0" cellpadding="3" cellspacing="1" bordercolor="38619E" > <tr> <th width="100" align="center" bgcolor="#E0E8F3">Territory</th> <th width="110" align="center" bgcolor="#E0E8F3">Employer</th> <th width="110" align="center" bgcolor="#E0E8F3">Job Title</th> <th width="50" align="center" bgcolor="#E0E8F3">Title</th> <th width="110" align="center" bgcolor="#E0E8F3">First Name</th> <th width="110" align="center" bgcolor="#E0E8F3">Last Name</th> <th width="70" align="center" valign="middle" bgcolor="#E0E8F3"> </th> </tr> <?php if($result) { for($i=0; $i<mysql_num_rows($result); $i++) { $id = trim(mysql_result($result, $i, "id")); $territory = trim(mysql_result($result, $i, "territory")); $employer = trim(mysql_result($result, $i, "employer")); $job_title = trim(mysql_result($result, $i, "job_title")); $title = trim(mysql_result($result, $i, "title")); $first_name = trim(mysql_result($result, $i, "first_name")); $last_name = trim(mysql_result($result, $i, "last_name")); echo "<tr>"; echo "<td>".$territory."</td>"; echo "<td>".$employer."</td>"; echo "<td>".$job_title."</td>"; echo "<td>".$title."</td>"; echo "<td>".$first_name."</td>"; echo "<td>".$last_name."</td>"; echo "<td><a href='admin_edit.php?id=".$id."'>edit</a> | <a href='admin_delete.php?id=".$id."'>del</a> </td>"; echo "</tr>"; } } ?> </table> <br /> </form> <p> </p></td> </tr> <tr> <td height="38"><table width="760" border="0" cellpadding="0" cellspacing="0"> <tr> <td width="200" height="35" align="center" bgcolor="#1B1C78" class=white><img src="images/topspacerblue.gif" alt="" width="1" height="3" /> <a href="disclaimer.html"><font color="#FFFFFF">Legal Disclaimer</font></a> </td> <td width="560" align="center" bgcolor="#0076CC" class=white><img src="images/topspacerlblue.gif" alt="" width="1" height="3" /> Copyright © 2006 - 2010 Limited. All rights reserved. </td> </tr> </table></td> </tr> </table> Immediate response is well appreciated. Thank you very much! Here is my code: // Start MySQL Query for Records $query = "SELECT codes_update_no_join_1b" . "SET orig_code_1 = new_code_1, orig_code_2 = new_code_2" . "WHERE concat(orig_code_1, orig_code_2) = concat(old_code_1, old_code_2)"; $results = mysql_query($query) or die(mysql_error()); // End MySQL Query for Records This query runs perfectly fine when run direct as SQL in phpMyAdmin, but throws this error when running in my script??? Why is this??? Code: [Select] 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 '= new_code_1, orig_code_2 = new_code_2WHERE concat(orig_code_1, orig_c' at line 1 I'm trying to update every record where one field in a row is less than the other. The code gets each row i'm looking for and sets up the query right, I hope I combined the entire query into one string each query seperated by a ; so it's like UPDATE `table` SET field2= '1' WHERE field1= '1';UPDATE `table` SET field2= '1' WHERE field1= '2';UPDATE `table` SET field2= '1' WHERE field1= '3';UPDATE `table` SET field2= '1' WHERE field1= '4';UPDATE `table` SET field2= '1' WHERE field1= '5'; this executes properly if i run the query in phpMyAdmin, however when I run the query in PHP, it does nothing... Any advice? The following code does what I want it to, but after it's done doing it, it won't stop loading. I believe it is because of the while loops (while(1) { ... } and while($data = ...) { ... }). I need while($data = ...) to read the data from the fsockopen. Thanks in advance function ircConnect($server, $port, $channel, $nick, $pass, $name, $msg) { set_time_limit(15); //If we put set_time_limit(0), the page won't timeout and never stops loading (The script works, that I'm aware of) $fp = fsockopen($server, $port); if(!$fp) die($php_errormsg); if(empty($php_errormsg)) { $error = "None!"; } else { $error = $php_errormsg; } echo "<table border='1' bordercolor='#FFCC00' style='background-color:#FFFFCC' width='400' cellpadding='3' cellspacing='3'>"; echo "<tr><td>Server</td><td>Port</td><td>Channel</td><td>Nick</td><td>Password</td><td>Error</td></tr>"; echo "<tr><td>$server</td><td>$port</td><td>$channel</td><td>$nick</td><td>$pass</td><td>$error</td></tr>"; echo "</table>"; fputs($fp, "USER aBot testchan.org TestchanIRCBot :TheBot\n"); fputs($fp, "PASS $pass\n"); fputs($fp, "NICK $nick\n"); fputs($fp, "JOIN $channel\n"); fputs($fp, "PRIVMSG $channel :$msg, my name is $nick!\n"); while($data = fgets($fp, 128)) { $ex = explode(' ', $data); echo nl2br($data); flush(); if($ex[0] == "PING") fputs($fp, "PONG " . $ex[1] ."\n"); } } http://i.imgur.com/E6j79.jpg TRUNCATE TABLE CubeCart_cats_idx; INSERT INTO CubeCart_cats_idx (productid, cat_id) SELECT productid, cat_id FROM CubeCart_inventory; I an running the following in MyPHPAdmin, but need this to be automated in a PHP script, how would I do it ? all help is appreciated, still new to PHP & MySQL many thanks D Hey guys, so I have this script:
<?php $databasehost = "localhost"; $databasename = "import"; $databasetable = "import"; $databaseusername="import"; $databasepassword = "password"; $fieldseparator = ","; $lineseparator = "\n"; $csvfile = "test.csv"; if(!file_exists($csvfile)) { die("File not found. Make sure you specified the correct path."); } try { $pdo = new PDO("mysql:host=$databasehost;dbname=$databasename", $databaseusername, $databasepassword, array( PDO::MYSQL_ATTR_LOCAL_INFILE => true, PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION ) ); } catch (PDOException $e) { die("database connection failed: ".$e->getMessage()); } $affectedRows = $pdo->exec(" LOAD DATA LOCAL INFILE ".$pdo->quote($csvfile)." REPLACE INTO TABLE `$databasetable` FIELDS TERMINATED BY ".$pdo->quote($fieldseparator)." LINES TERMINATED BY ".$pdo->quote($lineseparator)); echo "Loaded a total of $affectedRows records from this csv file.\n"; ?> Hi everyone! I've been working on a php script to replace links that contain a query with direct links to the files they would redirect to. 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 this is supposed to display the distinct first two characters of a string(billnmbr) in a combobox ive tried this on phpmyadmin and it returns 08.. which is correct when i run this on my server, it returns nothing.. there must be something wrong with the way i called the billnmbr does anybody have an idea on how i could call and display this correctly? thanks and have a nice day. <?php $mquery = "select distinct(substring(`billnmbr`, 1, 2)) from `tbltest`"; $mres =mysql_query($mquery) or die(mysql_error()); while($mrow=mysql_fetch_array($mres)){ $mm = $mrow['billnmbr']; echo "<option value=" . $mrow['billnmbr'] . ">"; echo $mrow['billnmbr'] . "</option>"; } echo " </select>"; ?> I have a db.php and inside it I filled out all the info needed:
<?php
define('DB_HOST', 'example.com');
define('DB_NAME', 'database_name');
define('DB_USERNAME', 'user_name');
define('DB_PASSWORD', '*******');
$odb = new PDO('mysql:host=' . DB_HOST . ';dbname=' . DB_NAME, DB_USERNAME, DB_PASSWORD);
?>
My problem is with the DB_HOST, is that my direct URL to the site? for example, google.com or is it like an IP?
Hello. This one is driving me up the wall. It used to work, I have a sports league database (MsSql) and one of the tables contains team Captain's first name, last name, etc.. To create a unique but simple login, the query searches for the captain's first initial of their first name + lastname + league. However since there is the remote possibility of two similar usernames (i.e. David Smith) if the last name is found, the query adds an integer to the end of the last name. So the result would look like this: DSmithM4A At the moment, my database is completely blank. However what is happening when I run the query below is I get: DSmith1M4A Can someone figure out why this code is not working correctly? If there is not more than one "Smith" no integer is supposed to be returned. There is something in the "Captain Count" that I think is not working correctly. Code: [Select] /* * Gather captian information */ $strCaptainFirstInitial = substr($arrPost['cpt_first'],0,1); $strCaptainLastName = preg_replace("/[^a-zA-Z]/","",$arrPost['cpt_last']); $strCaptainName = strtolower($strCaptainFirstInitial.$strCaptainLastName); $sqlCaptainSearch = sprintf ( $strCaptainSearch, $strCaptainName ); $resCaptainSearch = mssql_query($sqlCaptainSearch,$conDB); /* * Create unique captain login and Password */ $strCaptainCount = (mssql_num_rows($resCaptainSearch) > 0) ? mssql_num_rows($resCaptainSearch) : "" ; $strLeagueTypeInitial = substr($arrLeagueSearch['type'],0,1); $strCaptainLogin = $strCaptainName . $strCaptainCount . $strLeagueTypeInitial . $arrLeagueSearch['size'] . $arrLeagueSearch['division']; Hi all ! The following code has two similar queries. Query 1 and Query 2. While Query 1 works just fine, Query 2 simply fails persistently. I have not been able to figure out why?
I have checked every single variable and field names but found no discrepancy of any sort or any mismatch of spelling with the fields in the database.
I would be grateful if anybody can point out what is preventing the second query from returning a valid mysqli object as the first one does. Here is the code for the two.
<?php
$db_host = "localhost";
$db_user ="root";
$db_pass ="";
$db_database ="allscores";
//////////////// CHECKING VARIABLE FILEDS IN A UPDATE QUERY //////////////////////////////////
//////////////// QUERY 1 /////////////////////////////////////////////////////////////////////
/*
$db_database ="test";
$db_table ="users";
$RecNo = 1;
$field1 = 'name';
$field2 = 'password';
$field3 = 'email';
$field4 = 'id';
$val1 = "Ajay";
$val2 = "howzatt";
$val3 = "me@mymail.com";
$con = mysqli_connect($db_host,$db_user,$db_pass,$db_database) or die('Unable to establish a DB connection');
$query = "UPDATE $db_table SET $field1 = ?,
$field2 = ?, $field3 = ?
WHERE $field4 = ? "; // session terminated by setting the sessionstatus as 1
$stmt = $con->prepare($query);
var_dump($stmt);
$stmt->bind_param('sssi',$val1,$val2,$val3,$RecNo);
if($stmt->execute())
{
$count = $stmt->affected_rows;
echo $count;
}
//////////////// QUERY 1 END /////////////////////////////////////////////////////////////////////
*/
//////////////// QUERY 2 /////////////////////////////////////////////////////////////////////
$con = mysqli_connect($db_host,$db_user,$db_pass,$db_database) or die('Unable to establish a DB connection');
$table = 'scores';
$date = date('Y-m-d H:i:s');
/*
$prestr = "Mrt_M_";
$STATUS = $prestr."Status";
$S_R1 = $prestr."Scr_1";
$S_R2 = $prestr."Scr_2";
$PCT = $prestr."PPT";
$DPM = $prestr."DSP";
$TIMETAKEN = $prestr."TimeTaken";
*/
$STATUS = "Mrt_M_Status";
$S_R1 = "Mrt_M_Scr_1";
$S_R2 = "Mrt_M_Scr_2";
$PPT = "Mrt_M_PPT";
$DSP = "Mrt_M_DSP";
$TIMETAKEN = "Mrt_M_TimeTaken";
$TimeOfLogin = $date;
$no_of_logins = 10;
$time_of_nLogin = $date;
$m_scr_row1 = 5;
$m_scr_row2 = 5;
$m_ppt = 20;
$m_dsp = 60;
$m_time = 120;
$date = $date;
$RecNo = 24;
$query = "UPDATE $table SET
TimeOfLogin = ?,
no_of_logins = ?,
time_of_nLogin = ?,
$S_R1 = ?,
$S_R2 = ?,
$PPT = ?,
$DSP = ?,
$TIMETAKEN = ?,
$STATUS = '1',
TimeOfLogout = ?,
WHERE RecNo = ?";
$stmt = $con->prepare($query);
var_dump($stmt);
$stmt->bind_param('sisiiddssi',$TimeOfLogin,$no_of_logins,$time_of_nLogin,$m_scr_row1
$m_scr_row2,$m_ppt,$m_dsp,$m_time,$date,$RecNo);
if($stmt->execute()) echo " DONE !";
?>
Thanks to all
I am trying to simplify my coding work for my website and wanting to make it operate more professionally (as part of my training to better my programming skills, which I know will take a lot of time to do). One of the things I`d like to improve on it is to create tables and fields in my mysql database, but not have to go into phpmyadmin to do it. I know open source programs like Drupal can do this easily so that when you add modules, the user does not have to access the database to set it up. But looking at the codes, I`m unable to figure out how it works. How will I be able to program my code to do something like, I`ll have a form where I enter in what I want the new table to be called, what fields to add in and their specifications, where I could do this by just going into my website as the admin? I have a table korisnici in SQLite with INTEGER field aktivan that can have only 0 or 1 value (CHECK constraint). Field aktivan has value 0 but PHP returns value 1, why? Is this a bug? This is PHP code that I am running:
$sql = "SELECT ime, aktivan FROM korisnici WHERE lower(ime) = '" . $ime . "'" . " AND sifra = '" . $_POST["sifra"] . "'";
$result = $db->query($sql);
$row = $result->fetchArray(SQLITE3_ASSOC);
$row['aktivan'] = 1 but in table the value is 0. When I run same query in DB Browser for SQLite I get correct value 0. Is this a bug? I have the following php code that errors as indicated:
$query = $con->query('SELECT FILENAME, country, area, city FROM download WHERE FILENAME is not null');
Fatal error: Uncaught PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'country' in 'field list' in /home/larry/web/test/public_html/report1.php:47 Stack trace: #0 /home/larry/web/test/public_html/report1.php(47): PDO->query('SELECT FILENAME...') #1 {main} thrown in /home/larry/web/test/public_html/report1.php on line 47
The Select statement doesn't error when run in mysql shell or phpmyadmin. Here's the result of show create table download: localhost/test/download/ http://localhost/phpmyadmin/tbl_sql.php?db=test&table=download&token=5739c407033be3e118287bc7a9041c2c Current selection does not contain a unique column. Grid edit, checkbox, Edit, Copy and Delete features are not available. Your SQL query has been executed successfully. show create table download download CREATE TABLE `download` ( `ID` int(5) NOT NULL AUTO_INCREMENT, `LOG_TIME` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, `IP_ADDRESS` int(64) unsigned NOT NULL, `FILENAME` varchar(50) COLLATE utf8_general_mysql500_ci DEFAULT NULL, `country` varchar(50) COLLATE utf8_general_mysql500_ci DEFAULT NULL, `area` varchar(50) COLLATE utf8_general_mysql500_ci DEFAULT NULL, `city` varchar(50) COLLATE utf8_general_mysql500_ci DEFAULT NULL, PRIMARY KEY (`ID`), UNIQUE KEY `ID` (`ID`) ) ENGINE=InnoDB AUTO_INCREMENT=1266 DEFAULT CHARSET=utf8 COLLATE=utf8_general_mysql500_ci Does anyone have an idea why this is happening? |
|||||||