|
PHP - What's Wrong With My Query? Works On Sqlyog, But Nowhere Else.
I have a need to do a special sorting for a bunch of leads. The person who sees the leads (l.user) is looking at leads that are for a few different people (l.team_id). When I run my query in sqlyog, it gives me the results I am looking for, where the rank is used to sort. When I run my query through phpMyAdmin, php, or the MySQL command line, all of the ranks are NULL, so nothing gets sorted. All of these attempts are on the same database, on the same computer (Windows 7). Any ideas or help is appreciated.
SELECT
x.lead_id,
x.rank,
x.team_id,
x.status
FROM
(SELECT
l.lead_id,
l.team_id,
l.status,
CASE
WHEN @ps != l.team_id
THEN @rownum := 0
WHEN @ps = l.team_id
THEN @rownum := @rownum + 1
ELSE @rownum
END AS rank,
@ps := l.team_id
FROM
`system-leads` l
WHERE (l.user = 189905706)
AND l.status != "closed"
LIMIT 100000000 OFFSET 0) `x`
ORDER BY x.status ASC,
x.rank ASC,
x.lead_id ASC
I wonder why it works in sqlyog and not the others, but I need it to work regardless of what client I am using.
Similar TutorialsI'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? I am querying... $sql = "SELECT `messages_inbox`.`message_id`, `users`.`firstname`, `users`.`lastname`, `users`.`username` AS `from`, '${user_info['username']}' AS `to`, `subject`, LENGTH(`files`) AS `len`, 'inbox' AS `box`, DATE_FORMAT(`messages_inbox`.`time` ,'%T %D-%M-%Y') AS `time` "; $sql .= "FROM `messages_inbox` INNER JOIN `users` ON `messages_inbox`.`from_id` = `users`.`id` WHERE `to_id` = ${user_info['uid']} AND `messages_inbox`.`deleted` = 0 ORDER BY `messages_inbox`.`message_id` DESC"; and I am trying to output $displayName = ucwords("${message['firstname']} ${message['lastname']}"); by using $messages = pm_fetch_all($_GET['box']); I know my fetch works but for some reason firstname and lastname are only returning the logged in users first name and last name, not the person who sent the message. Seems to be a popular topic here - rewriting urls to have slashes instead of ?'s and ='s...
Fortunately, my rewrite is working, but php is displaying the wrong string, and I can't figure out why.
My .htaccess is:
Options +FollowSymLinks
RewriteEngine On
RewriteCond %{REQUEST_FILENAME} !-d
RewriteCond %{REQUEST_FILENAME}\.php -f
RewriteRule ^(.*)$ $1.php
RewriteCond %{SCRIPT_FILENAME} !-d
RewriteCond %{SCRIPT_FILENAME} !-f
RewriteRule ^file/(.*)$ ./file.php?str=$1 [L]
Going to something like example.com/file/anythinghere/ works but php says the $_GET['str'] is anythinghere.php/anythinghere and that's not right. In a case like that, I just need "anythinghere"
Is my htaccess wrong? There is nothing in my php code that is manipulating the string to do this. If I go to example.com/file.php?str=anythinghere "anythinghere" is displayed solo
Edited by idkwhy, 16 September 2014 - 08:48 PM. $q = "SELECT `users.id`, `users.username`, `users.firstname`, `users.lastname`, `users.accounttype`, `companies.companyid`, `companies.companyname`, `companies.companyoccupation`, `companies.country`, `companies.state`, `companies.city`, `companies.industry` FROM `users`, `companies` WHERE `users.id` = `companies.companyid`"; Error: Warning: mysql_fetch_assoc() expects parameter 1 to be resource, string given in /home/www-data/mysite.com/fresh.php on line 25 $sql_form_union = mysql_query("SELECT id, base FROM Match_1v1 WHERE show_id='$show_id' ORDER BY ordered UNION SELECT id, base FROM Segment_1 WHERE show_id='$show_id' ORDER BY ordered"); while ($form_union = mysql_fetch_array($sql_form_union)){ } I have developed a code for a login and seems to work well (No syntax error according to https://phpcodechecker.com/ but when I enter a username and a password in the login form, I get an error HTTP 500. I think that everything is ok in the code but obviously there is something that I am not thinking about. The code (excluding db connection): $id="''"; $username = $_POST['username']; $password = md5($_POST['password']); $func = "SELECT contrasena FROM users WHERE username='$username'"; $realpassask = $conn->query($func); $realpassaskres = $realpassask->fetch_assoc(); $realpass= $realpassaskres[contrasena]; $func2 = "SELECT bloqueado FROM users WHERE username='$username'"; $blockedask = $conn->query($func2); $blockedres = $blockedask->fetch_assoc(); $bloqueado = $blockedres[bloqueado];
//Login if(!empty($username)) { // Check the email with database I have a template page that opens with a GO click from a drop-down in a different page. The variable is being passed through to the URL and in the GET statement. However, my PHP code is producing the following error: Quote Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given in /data/23/2/100/53/2263053/user/2487001/htdocs/myalaskacenter/events/venues/test_venues-results.php on line 52 . Here is my code: Code: [Select] <?php //Assigns the venueCode tothe variable with a more convenient name $venueURL = $_GET['VenueCode']; @$DB = mysqli_connect('server','username','password','database'); if (mysqli_connect_errno()){ echo '</blockquote><br/><br /> Sorry, this webpage is temporarily unavailable.<br /> <a href="http://alaskapac.centertix.net/">Click here to search for events.</a>'; } else { ?> <?php $Query = "SELECT Events.EventTitle, DATE_FORMAT(Performance.startDateTime, '%W, %M %e, %Y') AS startDate, DATE_FORMAT(Performance.startDateTime, '%h:%i %p') AS startTime, Events.EventID, Events.thumb, Events.ShoWareEventLink, Events.tagline, Performance.category_id, Promoters.Presenter, Promoters.website, Events.startDATE, Events.endDATE, Events.EventOnSaleDate, venues.VenueName FROM ((Events LEFT JOIN Performance ON Events.EventID = Performance.EventID) LEFT JOIN Promoters ON Events.PromoterCode = Promoters.PromoterCode) LEFT JOIN venues ON Performance.VenueCode = venues.VenueCode WHERE venues.VenueCode=".$venueURL." AND Events.group_id=1 AND Performance.category_id!=2 AND Performance.category_id!=5 AND Performance.category_id!=7 AND Performance.category_id!=8 AND Events.EventOnSaleDate IS NOT NULL AND (Performance.PerfType='Public Event' OR Events.EventID='79') AND Performance.endDateTime >= now()-INTERVAL 1 DAY AND Events.PublishDate <= now() AND Events.startDATE IS NOT NULL ORDER BY Performance.startDateTime"; $Result = mysqli_query($DB,$Query); $NumResults = mysqli_num_rows($Result); ?> <?php if($NumResults=0){ echo "<p class='submenu'>$NumResults Performances</p>"; } while ($Row = mysqli_fetch_assoc($Result)){ $eventtitle = $Row['EventTitle']; $eventDate = $Row['startDate']; echo '<p><a href="' . $Row['ShoWareEventLink'] . '"><img src="https://alaskapac.centertix.net/UPLImage/' . $Row['thumb'] . '" alt="' . $Row['EventTitle'] . '" title="' . $Row['EventTitle'] . '" align="left" border="0"><span class="Heading3_blue">' . $Row['EventTitle'] . '</span>'; if($Row['FreeEvents']==TRUE){ echo '<img src="/images/free.gif" alt="Free Event" title="Free Event" width="80" height="80" align="right" border="0"></a><br />'; } elseif ($Row['EventOnSaleDate'] <= date("Y-m-d g:i a")){ /** IF ONSALEDATE<=NOW**/ echo '<img src="/images/logos/ctx/BUY_Tickets_gold.gif" alt="Buy Tickets" title="Buy Tickets" width="85" height="32" align="right" border="0"></a><br />'; } else {/** IF ONSALEDATE!<NOW**/ echo '<img src="/images/logos/ctx/AvailableSoon.png" alt="Available Soon" title="Available Soon" align="right" border="0"></a><br /><i>Tickets available ' . date("l, F j, Y", strtotime($Row['EventOnSaleDate'])) . ' at ' . date("g:i a", strtotime($Row['EventOnSaleDate'])) . '.</i>'; /** +ONSALEDATE **/ } echo '<br />Presented by <a href="' . $Row['website'] .'" target="_blank">' . $Row['Presenter'] . '</a>'; echo '<br />'.$Row['startDate']. ' at ' . $Row['startTime'].' - '.$Row['VenueName']; if ($Row['FreeEvents']==TRUE){ echo '<br /><br />'.$Row['BriefDescription']; } echo '<br /><br /></p><hr>'; } ?> <?php mysqli_free_result($Result); mysqli_close($DB); } ?> Here is my sample Results page: http://www.myalaskacenter.com/events/venues/test_venues-results.php?VenueCode=ACH This topic has been moved to MySQL Help. http://www.phpfreaks.com/forums/index.php?topic=347365.0 My variable `users`.`id` isn't being passed through correctly. $ms = 'SELECT `messages_inbox`.`from_id`, `messages_inbox`.`subject`, `messages_inbox`.`body`, `messages_inbox`.`files`, `messages_inbox`.`inbox` AS `box`'; $ms.= 'SELECT LEFT `users`.`firstname`, `users`.`lastname`'; $ms.= 'SELECT FROM `messages_inbox` INNER JOIN `users` ON `messages_inbox`.`from_id` = `users`.`id`' or die(mysql(error)); This topic has been moved to MySQL Help. http://www.phpfreaks.com/forums/index.php?topic=326252.0 ive looked at this for a while now and im not sure whats wrong, the error occured when i entered $cat and catagory into it, without that the query works perfectly fine, any help appreciated. <?php INSERT INTO newnotes (uid, name, catagory, location) VALUES ('1', 'test', '1001 Laws - Polceing', '/home/mikeh/public_html/uzEr Upl0ds/Alyssa O'Leary.doc') the <php tag is only to give color and make it less dull the query is from a die($query) statment I am having a warning which indicates there is a not valid mysql result, I think the problems lay down at the WHERE clause, but I am not sure. Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /storeprueba/sidebar.php on line 21 Code: [Select] $categoryurl = $_GET['categoryurl']; $sql= mysql_query("SELECT * FROM products, categories WHERE products.category = '$categoryurl' DESC LIMIT 6"); $productCount = mysql_num_rows($sql); // line 21 if ($productCount>0 ) { while($row = mysql_fetch_array($sql)) { $id= $row["id"]; $product_name= $row["product_name"]; $price = $row["price"]; $category = $row["category"]; $subcategory = $row["subcategory"]; $location = $row["location"]; $date_added = strftime("%b %d, %Y", strtotime($row["date_added"])); thanks. Hey guys, I have a problem that I find rather strange, but maybe that's just me. I want to output a list of news that is stored in a MySQL database. I have coded paging ($start in the example below), but below I have narrowed my code down and removed code that is not related to the problem. The problem is that my script does not output the first row. That is, if I limit the query below from 0 to the next 10 (LIMIT 0, 10), the result will start at row 2. The same happens if $start is 10; news 12 will be the first one rather than 11. I have tried to put the numbers directly into the SQL query instead of $start, but with the same result. I have tested the query in phpMyAdmin, and the query works fine there. But for some reason, why PHP script does not output the first result. I guess I have made some silly mistake, but at the moment, I do not see it. <?php // Connect to MySQL & select db $start = 0; $result = mysql_query("SELECT * FROM Content ORDER BY Time ASC LIMIT $start, 10"); if ($result) { if (mysql_num_rows($result) > 0) { $row = mysql_fetch_assoc($result); while ($row = mysql_fetch_array($result)) { echo '<div class="someClass">' . $row['title'] . '</div>'; } } } ?> Thanks in advance! Hi everyone. I'm stuck on the following query. I need to display all the fields listed below on a page, but linked via communications.CommID. I'd appreciate any assistance you can provide. thank you. Code: [Select] <?php $result = mysql_query("SELECT records.NameFirst_1, records.NameLast_1, records.CompanyName, records.CompanyBranch, records.CompanyReferenceNumber, records.CaseOwnerSelect, communications.ConversionType, communications.Contact, communications.ContactFrom, communications.CommID, communications.ContactPosition, communications.ContactTelephone, communications.ContactEmail, communications.ContactFax, communications.CallDate, communications.CallTime, communications.ActionTextField FROM records INNER JOIN communications ON records.IDNumber = '$IDNumber'") or die(mysql_error()); $row = mysql_fetch_array($result); ?> This topic has been moved to MySQL Help. http://www.phpfreaks.com/forums/index.php?topic=307477.0 I'm having trouble with a simple SELECT query. I just cannot figure out what the problem is... <?php //Include database connection details include 'login/config.php'; //Connect to mysql server $link = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD); if(!$link) { die('Failed to connect to server: ' . mysql_error()); } //Select database $db = mysql_select_db(DB_DATABASE); if(!$db) { die("Unable to select database"); } $qry="SELECT * FROM members"; $result = mysqli_query($link, $qry); echo "<table>"; while($row = mysqli_fetch_array($result, MYSQL_ASSOC)) { $getid = ($row['member_ID']); $firstname = ($row['firstname']); $lastname = ($row['lastname']); $email = ($row['email']); echo "<tr><td>$firstname</td><td>$email</td></tr>"; } echo "</table>"; ?> I know I have a connection to the DB, and I know that the query will return values as I have tested in in phpmyadmin. Can anyone see anything obvious I am missing? Thanks so my situation is something like this , i'm trying to fetch user details based on `id` that isset is getting, but some how the `variable that contains the $_GET value doesn't work` in query but when i put an static value to pdo query then it works and show the result. i have checked by doing `var_dump` of variable `$user` before query and it shows the correct value but not working in query. Below is the code i'm working with: I 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`])) {
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? |
|||||||