Function/Trigger already in use?
Your issue is that the update_users_trigger trigger makes changes to the contents of the table users, while the query that is triggering the execution of this trigger also uses the table users. You will need to adjust your query so that this deadlock doesn't occur. It isn't clear which fields...
Can't find FULLTEXT index while using IN BOOLEAN MODE
mysql,sql,full-text-search,mariadb,match-against
There could be many several factors why in boolean mode not working like mysql version you are having, storage engine etc. However its highly recommended to use fulltext index when you are doing match .. against since this is what it is meant for. Now coming to the point will...
MySQL trigger help, capturing a variable from php insert into
You are on the right path: IF (SELECT true FROM redcap_encryption WHERE ProjectID=NEW.project_id AND FieldName=NEW.field_name).... ...
MySQL Query returning strange values
You need to join by account_id and also question_id SELECT * FROM `quiz_questions` INNER JOIN `quiz_answers` ON `quiz_questions`.`account_id` = `quiz_answers`.`account_id` AND `quiz_questions`.`question_id` = `quiz_answers`.`question_id` WHERE `quiz_questions`.`account_id` = '1840979156127491' ORDER BY `quiz_questions`.`question_id` ASC LIMIT 5 ...
I Want to fetch SQL Records in MySQL of current Year
Try this: SELECT count(enq.`enquiryId`), Month(enq.`date`), Year(enq.`date`) FROM enquiry enq WHERE Year(enq.date)=somevalue --2015 for example GROUP BY MONTH(enq.`date`) ...
Using VLOOKUP formula or other function to compare two columns
If data in your first table starts at A2, and your other column starts at D2, then use in E2 =VLOOKUP(D2,$A$2:$B$17,2,0) Copy down as needed....
Select all none null values from all columns in a table
select @variable will just return you a value of variable. You need to use some dynamic SQL I believe. Maybe smth like exec('select ' + @colname + ' from ' etc) will work for you (at least it will work in MS SQL server).
How to check if data already exists then randomly generate new data from an Array
In my personal opinion, NEVER try to get data from an array within quotes! Always do it outside of quotes; especially in multi-denominational arrays. '$input[$rand_keys[$i]]' should be rewritten as '".$input[$rand_keys[$i]]."' OR '{$input[$rand_keys[$i]]}'. In my opinion it is better to do it outside of quotes instead of using { }. ...
How to program a recurring billing/invoice system using PHP and MySQL
Cron sounds good. (Also it is worth to mention the MySQL Event Scheduler, but again I would go for a Cronjob) A copy would be something like this SQLFIDDLE: create table t ( id int, d date ); insert into t values( 0, CURDATE() ); insert into t values( 2,...
phpmyadmin collation: latin and cyrillic
mysql,character-encoding,collation
When you create your database, you can choose a default... Character set to define how your characters are stored. Collation to define how your characters are sorted and searched. You give a command like this: CREATE DATABASE mydata CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci phpMyAdmin has a dialog box...
php mysql select insert on different DBs in same server
If your connection has rights to both databases, there is no need to have two connections. A database connection is a connection to the server, not to a specific database (although you can select a default database within the connection). What you can do is normal INSERT INTO SELECT within...
C# - Can't connect to remote MySQL server
When connecting to a MySQL-Database I always used the MySQL Connector you can get here: https://dev.mysql.com/downloads/connector/net/6.9.html You have to import the MySQL namespaces to your project and then you can use the MySQLConnection instead of the SQLConnection that is, as far as I know, only for MSSQL servers. http://www.codeproject.com/Tips/423233/How-to-Connect-to-MySQL-Using-Csharp...
MySQL group_concat() function with join operation
If I understand your requirements, you want to bring back all the buildings and faculties for a school when they have one of the required ones. There are possibly a couple of ways to do this. You could use a couple of sub queries to find schools that have the...
Loop through database table and compare user input
If you are only looking for fields that match the input, you'll want to search the database using the input string. In other words, write your query string so that it only gives you results that match the user input. This will be much faster than searching through every returned...
MySQL 4 Table Join
What you probably need is a union of two sets: songs created by people user with id=6 follows songs reshared by people user with id=6 follows Since you haven't provided table schemas I'm using names you provided in the query. ( SELECT DISTINCT s.id, s.last_updated FROM follows f JOIN tracks...
Syntax error in Insert query in Mysql in VB.Net
you miss the closing parenthesis for the values list: Dim cmd1 As New OdbcCommand("insert into party values('" + pcode_txt.Text + "','" + Trim(UCase(name_txt.Text)) + "','" + Trim(UCase(addr_txt.Text)) + "','" + phone_txt.Text + "','" + combo_route.SelectedItem + "','" + combo_area.SelectedItem + "')", con) My answer is perfectly fit to your question...
MySQL Case When Statement
Use HAVING clause: SELECT City, SUM(CASE WHEN name2015 = 'Jane' THEN income2015 ELSE 0 END) income2015_total, SUM(CASE WHEN name2020 = 'Jane' THEN income2020 ELSE 0 END) income2020_total from finaldata GROUP BY City HAVING income2015_total <> 0 OR income2020_total <> 0 I have added ELSE 0 in the CASE expressions, so...
Group by date mysql and codeigniter
You need to ditch the star '*' and specify the expressions you want to return. To get a total, you need an aggregate function, and to get just the date portion of date_time, you can use the DATE() function. $this->db->select('DATE(a.date_time) AS date, SUM(a.value) AS total_value'); And you want to GROUP...
How do I display my mysql table column headers in my php/html output?
Note: You can just make a single file out of it to achieve your wanted output Use mysql_real_escape_string() to sanitize the passed-on value to prevent SQL injections You should use mysqli_* instead of the deprecated mysql_* API Form them in a single file like this (display.php): <html> <form method="post" name="display"...
Notice: Array to string conversion in “path of php file” on line 64
Curly brackets are your friend when inserting variables into double quoted strings: $main_query=oci_parse($connection,"INSERT INTO ROTTAN(NAME,ROLLNO) VALUES('{$array[$rs][0]}','{$array[$rs][1]}')"); ...
Read db and write to file in python
You can use the codes above:(each row inserted as new lines) conn = MySQLdb.connect(host="localhost",user="root",passwd="",db="mydb") cursor = conn.cursor(MySQLdb.cursors.DictCursor) cursor.execute("SELECT * FROM books") result_set = cursor.fetchall() field_names = [val[0] for val in cursor.description] with open('yourfile.txt', 'a') as f: for row in result_set: line = ','.join(row[field_name] for field_name in field_names) f.write(line) ...
Mysql Join - Filter results based on two max values
It is strange to see that you have separate columns for date and time, but maybe this is necessary to store repeated times (11:00 daily) or dates without times. Otherwise you should use a datetime instead. What you are looking for is the latest datetime or timestamp in a week....
C# MySQL Parameters.AddWithValue
You try to add all your 52 parameter and their values with one AddWithValue method. You can't do that. First of all, you need to define all your parameters in your command with your column names like; command.CommandText = "INSERT INTO tb_mitarbeiter (Vorname, id, projectnummber....) VALUES (?name, ?id, ?projektnummer....)"; Then...
Can Rails deal with DB uniqueness without index?
Because there is no need for other ways. Under the hood it's all the same: when you define a UNIQUE constraint, a UNIQUE index is created on that table to enforce it. Question from DBA.SE: When should I use a unique constraint instead of a unique index? So for a...
Unable to connect to your database server using the provided settings in Codeigniter
$db['default']['password'] = ''; there is an space, remove it use below settings $db['default']['hostname'] = 'localhost'; $db['default']['username'] = 'root'; $db['default']['password'] = ''; $db['default']['database'] = 'test_bs'; $db['default']['dbdriver'] = 'mysql'; $db['default']['dbprefix'] = ''; $db['default']['pconnect'] = TRUE; $db['default']['db_debug'] = TRUE; $db['default']['cache_on'] = FALSE; $db['default']['cachedir'] = ''; $db['default']['char_set'] = 'utf8'; $db['default']['dbcollat'] = 'utf8_general_ci';...
Ignore Group if LIMIT is not reached in MySQL
You can use the having clause to filter out the groups you don't need, keeping only the groups where there are more than 4 dates: SELECT Fruits, SUM(Ordered), Date FROM table GROUP BY Date HAVING COUNT(Date) > 4 ...
Getting total for Invoiced Paid and the amount owing for each client
The problem is the non existing relation between the invoice and the payments. This makes it a bit "dirtier" ;) Try this: select p.id, sum(i.total), sum(p.amount), sum(i.total) - sum(p.amount) as outstanding from ( select c.id, coalesce(pay.amount,0) as amount from (select ClientId, Sum(Amount) amount from payments group by ClientId) as pay...
Entering data into mysql database using php
This shoud work, if not then check your usename and password... <?php $servername = "localhost"; $username = ""; $password = ""; $dbname = "first_db"; $pathway = $_POST['username']; username - is the name of your input. // Create connection $conn = mysqli_connect($servername, $username, $password, $dbname); // Check connection if (!$conn) {...
How to post time value in database in PHP
It is very simple: You code here: $id_time = date("d-m-Y",time()); Please UPDATE THAT INTO; $id_time = date("Y-m-d",time()); Your sql is ok. And I hope that will work....
php form get parameters from url and store it until the final page
It looks like - but I may be wrong so please clarify any incorrect assumptions - but it looks like there are potentially several issues here: Get and post are different. Use $_REQUEST['var'] to select GET or POST (I think POST overwrites GET values in this situation, if both are...
How can I create missing date records for each employee based off a limited calendar?
Create a table that contains all the possible dates in the range, @alldates Then insert your missing records with something like this: Query INSERT INTO dbo.timeclock SELECT id ,d.punchtime ,'no time entered' FROM ( SELECT DATE ,id FROM @alldates d CROSS JOIN ( SELECT DISTINCT id FROM dbo.timeclock )...
WHERE If condition is not met get all values including NULL
Another shorter form where @LastName in ('All', LastName); SqlFiddle...
Scrambling(randomize) row id's in mysql table
How about this? alter table add column randorder double; update table set randorder = rand(); create index idx_table_randorder on table(randorder); Then you can "shuffle" the records by doing: select t.* from table t order by randorder; This will use the index so it will be fast. It is stable, so...
If I export my database with phpmyadmin will it lock my tables or take my database down?
The answer is no, tables won't be locked, database won't be down. But, if your database is large and it takes long time to backup it, you can sometimes expect performance degradation(slow SQL queries from your application).
MySQLSyntaxErrorException in SQL syntax
java,mysql,jsp,mysql-connector
Change ResultSet rs = selectUser.executeQuery(query); to ResultSet rs = selectUser.executeQuery(); when you already prepared the statement in connection.prepareStatement(query); then why to pass the query again in selectUser.executeQuery(query);...
compare today's date with unix timestamp value in database
If I understand correctly you have a unix timestamp in a varchar field and you can't change this. If you compare the unix timestamp directly you will only get results that match the exact second of the timestamp. You can use FROM_UNIXTIME() to convert the timestamp in a date value...
SQL only one cell from column can have X value
Have a separate table with one row that contains the mainOfferName of the row that you want. Then query as: select yt.*, (nt.mainOfferName is not null) as valueFlag from yourtable yt left join newtable nt on yt.mainOfferName = nt.mainOfferName; When you want to change the value, change the value in...
Displaying MySQL results in a single table
Try getting all the columns out first, then add it to the sql query, no need to loop the database query. $mark = $_POST['mark']; if (isset($_POST['mark']) && is_array($_POST['mark'])) { echo "<table border='1'>"; echo "<tr>"; for ($i = 0; $i < count($mark); $i++) { echo "<th>" . $mark[$i] . "</th>"; }...
timestamp SQL to Excel
The ###### is shown in MS Excel when the data in a cell is too long for the column width.... the data inside the cell is still correct, as you can see if you select one of those cells and look at the value displayed in the cell content bar...
how to get records from field having contain field ids like (1,2,3) in a field in mysql?
Change FIND_IN_SET(ex_students.s_ids, students.id) to FIND_IN_SET(students.id, ex_students.s_ids) ...
Geolocation to find nearest place [on hold]
You can definitely use Google's Geolocation API to get the user's current location, then with some Javascript, you can determine the closest park.
How to create multiple jquery form fields and insert in mysql database, without using mysql_real_escape_string
Your problem has nothing to do with jQuery and the form. It is just highly recommended to prevent SQL injection, an attack in which an attacker injects SQL commands into your DB Query by posting it in your form. That's why any data that comes from an untrusted source (eg...
Inner Join 3 tables pl/sql
The issue is that you are using the alias C where you should not, with the count function. This: C.Count(C.column6) should be: Count(C.column6) and the same change applies in the order by clause (which might be counting the wrong column - should it not be column6?): order by C.count(column5) desc...
How to call MySQL view in Struts2 or Hibernate
java,mysql,hibernate,java-ee,struts2
You can simply create an Entity, that's mapping the database view: @Entity public class CustInfo { private String custMobile; private String profession; private String companyName; private Double annualIncome; } Make sure you include an @Id in your view as well, if that's an updatable view. Then you can simply use...
PHP/MySQL only updates if the variable is less than a few characters
From the comments we've debugged this to being an apostraphe/quote in the data being passed to the query. To resolve this with your current DB driver use, mysql_real_escape_string, http://php.net/manual/en/function.mysql-real-escape-string.php. You should switch to MySQLi or PDO though in the future and use prepared statements. Here's a functional usage (untested, so...
MySQL substring match using regular expression; substring contain 'man' not 'woman'
A variant of n-dru pattern since you don't need to describe all the string: SELECT '#hellowomanclothing' REGEXP '(^#.|[^o]|[^w]o)man'; Note: if a tag contains 'man' and 'woman' this pattern will return 1. If you don't want that Gordon Linoff solution is what you are looking for....
How to show Only a selected Number of Database entries on the page [on hold]
You can achieve the pagination either by client side implementations or server side. Client side (using Java Script/JQuery) and Server Side (using PHP) is preferable. For client side, just the following link For Server side PHP Pagination...
How do I find records in one table that do exist in another but based on a date?
select d.`name` from z_dealer d where (select count(*) from z_order o WHERE o.promo_code = d.promo_code AND o.date_ordered > '2015-01-01') = 0 ...
Fail to create array with query values
Your variable assignments are all wrong. They should be: $range = $_COOKIE["range"]; $longitude = $_COOKIE["longitude"]; $latitude = $_COOKIE["latitude"]; When you try to use a non-numeric string as a number in an arithmetic expression, it's treated as 0. So the result of your code was effectively: $le = 0 * $onemile;...
Uploading PNG to Website Server
You're not declaring $image_width or $image-height and you are referencing $image instead of $source_image in imagecopyresampled(). I too was getting a plain white image, but after this I get the expected result: $image = $_FILES['file']['tmp_name']; $image_name = $_FILES['file']['name']; $ext = pathinfo($image_name, PATHINFO_EXTENSION); $location = "Profiles/{$user}/Picture/{$image_name}"; $new_image = imagecreatetruecolor(100, 100); $source_image...
Retrieve Values As Column
If types are fixed (just IMPRESSION and CLICK), you could use a query like this: SELECT headline, SUM(tracking_type='IMPRESSION') AS impressions, SUM(tracking_type='CLICK') AS clicks FROM tracking GROUP BY headline ...
Improving SQL Queries
I understand your problem. You have some column-rule. Which is globally used in all app. For example in my case there was 'status' column, and there were some logical meaning called 'important', which worked if column had one of the certain values in the set. So, everywhere, to check if...
PHP: While loop not working after adjusting SELECT for SQL injection prevention
php,mysql,select,sql-injection,associative-array
You cannot bind column and table names, only data. You need to specify the table and then bind for your '%calendar weekday%'. $stmt = $conn->prepare("SELECT " . $selectLang . " FROM `TranslationsMain` WHERE `location` LIKE ? ORDER BY `sortOrder`, " . $selectedLang); $stmt->bind_param('s', $calendar_weekday); ...
select: result based on occurrence of explicit value
Write a subquery that gets the number of occurrences of each weight, and join with this. Then you can test the number of occurrences to decide which field to put in NewPrice. SELECT f.*, IF(weight_count = 1, Price, ReducedPrice) AS NewPrice FROM fonts AS f JOIN (SELECT weight, COUNT(*) AS...
Group and order by a column but donot include that column in results
Move the column from SELECT clause to ORDER BY clause: SELECT `newel_inventoryKeywordIdDictionaryId`.`inventoryId` FROM `newel_inventoryKeywordIdDictionaryId` , `newel_inventoryDictionary` WHERE `newel_inventoryKeywordIdDictionaryId`.`dicId` = `newel_inventoryDictionary`.`dicId` AND ( `newel_inventoryDictionary`.`word` = 'alabaster' OR `newel_inventoryDictionary`.`word` = 'chess' ) GROUP BY inventoryId ORDER BY COUNT(`newel_inventoryKeywordIdDictionaryId`.`inventoryId`) DESC; ...
php foreach loop insert data feed back repeat everytime
Before you start your foreach loop set a flag like: $error = false; Then in your loop if(!mysql_query($insert_query)) { $error =true; } And after the loop has closed if($error){ echo "problem show"; }else{ echo "successfully register"; } ...
MYSQL, PHP, PDO Check if row exists [on hold]
You are binding the exact string '$steamId' to the id so that it will search for an user with the id of '$steamId'. $stmt->bindParam(":id", $steamId, PDO::PARAM_STR); Also you can remove the quotes from the select statement. $stmt = $conn->prepare("SELECT Steam_id from Account_info WHERE Steam_id = :id"); ...
Error when trying to install app with mysql2 gem
mysql,ruby-on-rails,ruby,ruby-on-rails-4
The error log says: ld: library not found for -lssl So, you need to install libssl: brew install openssl Hope it helps....
Get the last record even when the table contains one record
select top 1 * from mytable order by insert_date desc
Comparing two values in the same row and change if needed
Below query will work, unless you need to do query optimization and reduce the locking period UPDATE Product SET Voorraad = Minvoorraad WHERE Minvoorraad > Voorraad ...
how to convert the text outputted from a database to links?
According to your code. if ($result->num_rows > 0) { $htmlLink = ''; while($row = $result->fetch_assoc()) { $id = $row["id"]; $title = $row["title"]; $htmlLink .= "<a href='movie.php?{$id}'>id: {$id} - Name: {$title}</a><br>"; echo $htmlLink; } } else { echo "0 results"; } Then on your movie.php page use $_GET[] to get the...
MySQL: Select several rows based on several keys on a given column
If you are looking to find the records matching with both the criteria here is a way of doing it select `item_id` FROM `item_meta` where ( `meta_key` = 'category' and `meta_value` = 'Bungalow' ) or ( `meta_key` = 'location' AND `meta_value` = 'Lagos' ) group by `item_id` having count(*)=2 ...
mysql detect field value change
Here's a way to do it using variables: SELECT id, `timestamp`, ver_fw FROM ( SELECT id, `timestamp`, ver_fw, IF ( @prev_ver <> ver_fw, IF (@prev_ver := ver_fw, 1, 1), IF (@prev_ver := ver_fw, 0, 0)) AS IsDifferent FROM tbl_geodata CROSS JOIN (SELECT @prev_ver := '-1') AS var WHERE imei LIKE...
MySQL - How can I know my query is tuned?
Except for trivial queries, there is no way to know if you have the optimal query & indexes. Nor can you get a metric for how well designed the schema and application are. 3 seconds on a cold system for a 3-way JOIN with "Rows" of 409, 45, 1 is...
Having two arrays in variable php
php,mysql,arrays,variables,multidimensional-array
The explode function is being used correctly, so your problem is further up. Either $data[$i] = mysql_result($result,$i,"data"); isn't returning the expected string "2015-06-04" from the database OR your function $data[$i] = data_eng_to_it_($data[$i]); isn't returning the expected string "04 June 2015" So test further up by echo / var_dump after both...
How to search images by name inside a folder?
This looks like a job for glob, which returns an array of file names matching a specified pattern. I'm aware of the other answer just posted, but let's provide an alternative to regex. According to the top comment on the docs page, what you could do is something like this:...
I need help using a class in a function in PHP
$db->dbc(); this has no effect since you do not store the return value in a variable and then use this variable to make the call to prepare(). Also you're not storing the PDO object in a member variable but in a local variable in the constructor method -> falls out...
Using Sum in If in Mysql
Using least would be much easier: SELECT LEAST(SUM(my_field), 86400) FROM my_table ...
Fatal error in if stament [duplicate]
Just change the condition to: if(isset($_REQUEST['userid']) && $_REQUEST['userid'] > $user_hack) isset tells is a variable is set, while this statement may be true or false, on which you cannot call isset function. Until you check if(isset($_REQUEST['userid'])), you cannot assign it to $userid variable....
How to get entries from SQL that only appear in certain value?
You can select all Transmission = 'Inventory' ids and filter out those exist in Transmission in('Starting', 'Stopping'): select distinct(DeviceID) from YourTable WHERE Transmission = 'Inventory' and DeviceID not in ( select distinct(DeviceID) from YourTable WHERE Transmission in('Starting', 'Stopping') ); SQL Fiddle: http://sqlfiddle.com/#!9/81896/12...
Query how often an event occurred at a given time
This could be done using user defined variable which is faster as already mentioned in the previous answer. This needs creating incremental variable for each group depending on some ordering. And from the given data set its user and date. Here how you can achieve it select user, date, purchase_count...
SQL Group By multiple categories
Just include a case statement for the group by expression: SELECT (CASE WHEN Categories.name like 'Cat3%' THEN 'Cat3' ELSE Categories.name END) as name, sum(locations.name = 'loc 1' ) as Location1, sum(locations.name = 'loc 2') as Location2, sum(locations.name = 'loc 3') as Location3, count(*) as total FROM ... GROUP BY (CASE...
creating stored procedure in mysql calculate profit from product table
mysql,sql,database,stored-procedures
You forgot the () after the procedure name. Also, you should set a delimiter: DELIMITER // CREATE PROCEDURE sp_profit() BEGIN SET @v1:= (select sum( cost_price * current_stock) from product); SET @v2:= (select sum( selling_price * current_stock) from product); SELECT (@v2 - @v1); END; // ...
SQL result table, match in second table SET type
Example of junction/intersect table usage. create table subscription_plans ( id int not null auto_increment primary key, -- common practice name varchar(40) not null, description varchar(255) not null, price decimal(12,2) not null -- additional indexes: ); create table pricing_offers ( id int not null auto_increment primary key, -- common practice name...
concatenate field names in a mysql update with inner join
You can try as per below- UPDATE products pr INNER JOIN sub_categories sc ON sc.id = pr.sub_category SET slug = REPLACE(TRIM(LOWER(CONCAT(sc.subcat_name,'.',products.product_name))),' ', '-'); ...
Changing values after MySql DB Collection
There are several possible ways to rename the fields, but the easiest may be to do it within your SQL statement: $query = "SELECT title AS `MainTitle`, surname AS `Lastname`, email AS `MainEmail` FROM app2"; ...
Select customers without address
You need to put brackets round the last part of your query to indicate that it's a subquery: SELECT id_customer, customer.email FROM ps_customer WHERE id_customer NOT IN ( SELECT id_customer FROM ps_address ); ...
Trying to rewrite mysql_* to pdo
I don't know the source of the array $arr = array();, but it is assigned to null before the insert query. So it means, literally you are inserting nothing into the database. So check your array well, maybe it was to be like $arr = array('name'=>'My Name', 'url'=>'url', 'email'=>'my email',...
Error 1136: Column dosent match value count at row 1
remove '[email protected]' INSERT INTO cliente (CFCL, CognomeCL, NomeCL , SessoCL , ComuneNascitaCL, DataNCL, IndirizzoCL, TelefonoCL, CittadinanzaCL, PatenteCL, DataSCL) VALUES ('MNA12OSQWDEWEWO8', 'Cognome', 'nome', 'F', 'Abbasanta', '1995-07-07', 'via pisa, 21', '0803597845', 'italiana', 'ba1234567q', '2020-07-07'); ...
Show tables with LIKE and NOT REGEXP
Try this: SHOW TABLES FROM database WHERE Tables_in_database LIKE 'wp_%' AND Tables_in_database NOT REGEXP 'wp_[0-9]+' ie, remove the % character from regex....
MySQLdb Python - Still getting error when using CREATE TABLE IF NOT EXISTS
MySQL is actually throwing a warning rather that an error. You can suppress mysql warnings like this : import MySQLdb as mdb from warnings import filterwarnings filterwarnings('ignore', category = mdb.Warning) Now the mysql warnings will be gone. But mysql errors will be shown as usual Read more about warnings at...
PHP / MySQLi: How to prevent SQL injection on INSERT (code partially working)
php,mysql,mysqli,sql-injection,sql-insert
In the New PHP code snippet, you are still vulnerable to injections. You are using a prepared statement in the insert part, but you are not actually using the preparations strengths correctly. When creating a prepared statement, you create a query in which you add placeholders instead of the raw...
Select most recent entry where timestamp is from the past hour
select * from table where TIMESTAMPDIFF(HOUR, `timestamp`, NOW())=0 ORDER BY `timestamp` DESC LIMIT 1 ...
Order by count not sorting the records correctly?
Try this SELECT count(receiver_id) as total_receiver FROM gr_group_memberships INNER JOIN gr_group on gr_group_memberships.group_id = gr_group.id GROUP BY gr_group_memberships.receiver_id ORDER BY gr_group_memberships.receiver_id DESC I think it will worked what you want...
Convert March 2015 to 03/2015 and make into date
STR_TO_DATE works for me with a format of '%M %Y': mysql> select str_to_date('July 2015', '%M %Y'); +-----------------------------------+ | str_to_date('July 2015', '%M %Y') | +-----------------------------------+ | 2015-07-00 | +-----------------------------------+ 1 row in set (0.10 sec) Expand your question if there's some reason you can't do the same. To turn that into...
Awarding points to an applicant only once:-no duplicate
Create a database that holds the admin's username and the applicant they voted for. So every time they click the vote button it stores a new record in the database. To prevent them from multiple votes, every time they vote check to see if the records exists by using the...
UPDATE SET = SELECT FROM
The error itself is pretty self explanatory, you are selecting from the table you are updating. Instead, you can use JOIN in the update statement. Try this: UPDATE branches b LEFT JOIN companies c ON c.id = b.company_id SET b.name = CONCAT(c.name, " ", b.subsurb); Note that because you're using...
Is it possible to connect to remote DB while working in localhost?
Yes, of course it is. You'll need to whitelist your development servers on the remote server's firewall and allow remote access to the database. If you're using cPanel, there are options there to do so, but you may need to speak to your host as well.
Escape password in mysqldump command
quote the password mysqldump -u fred7 -p'asdf^555^666' if any of the following * ? [ < > & ; ! | $ ( ) perhaps ^ too...
MySQL Select where count
Your subselect in the WHERE clause is making comparisons on the aggregate COUNT(u.ULN) value, and therefore really belongs in a HAVING clause. It should be significantly more performant there, because the subselect would be evaluated for every row returned by the outer query while the HAVING filter will only be...
IBM Cognos _days_between function not working
The Cognos _days_between function works with dates, not with datetimes. Some databases, like Oracle, store all dates with a timestamp. On a query directly to the datasource, try using the database's functions to get this data instead. When possible, this is always preferable as it pushes work to the database,...
Unique movies by UNION the results of two join, MYSQL
Distinct can be run inside a count in mysql which is pretty handy. select Count(Distinct answer) from ( SELECT member_CROWD.worker_Id, member_CROWD.Unreliable, Answer_CROWD.qId, Answer_CROWD.answer FROM member_CROWD JOIN `Answer_CROWD` ON member_CROWD.worker_Id = Answer_CROWD.worker_Id WHERE member_CROWD.Unreliable = '0' AND Answer_CROWD.qId = 'q8' GROUP BY Answer_CROWD.answer UNION SELECT member.mem_id, member.Unreliable, Answer.qId, Answer.answer FROM member...
how to get more than one max records in MySQL
In MySQL, the query is a bit more complicated. One method is using two subqueries with aggregation: select t.* from (select t.id, count(*) as A_plus from take t where t.grade = 'A+' group by t.id ) t where t.A_plus = (select max(A_plus) from (select t.id, count(*) as a_plus from take...
Purging Database - Count purged/not-purged tables
mysql,sql,sql-server,database,stored-procedures
The only way to do this is to manually run a count(*) on all of your tables filtering on the particular date field. The reason for this is because one table might have a column "CreatedDate" that you need to check if it's >30 days old, while another might have...
Use SQL output 1 by 1
Store your results arrays in an array: <?php $RankSql = 'SELECT * FROM games WHERE Genre LIKE "%'.$Genre.'%" ORDER BY Score DESC limit 5'; $Rankresult = mysql_query($RankSql); $games = array(); while($RankOrder = mysql_fetch_array($Rankresult)) { $games[] = $RankOrder; }; // ex: third game (remember, arrays start at zero) echo $games[2]['Game']; ?>...
Get the record with the min distance
You get no result because the minimum distance returned by the subquery is not filtered with the filter you give to the parent query. Thus the min distance is 60, but there is no rows that match this speed having at the same time mac = ' 10:A5:D0:06:C6:E9' stop_name =...
Temporarily Number Rows to Group by in MYSQL
You can do this using variables: select s.*, floor( ((@rn := @rn + 1) - 1) / 5) as group_number from sample s cross join (select @rn := 0) params order by date; ...
Symfony2 creating and persisting entity relationships
When you create two entities with a one-to-one relationship, both entities need to be persisted either explicitly or by using cascade persist on one side of the relationship. You also need to explicitly set both sides of the relationship. Doctrine - Working with Associations - Transitive persistence / Cascade Operations...
how will i order this query by DoctorName
Add an ORDER BY statement to your query: $q=mysqli_query($link,"Select * from doctor where status='". $stat ."' ORDER BY `doctor_last_name`"); ...