FAQ Database Discussion Community


MySQL substring match using regular expression; substring contain 'man' not 'woman'

mysql,regex
I have an issue while I fetch data from database using regular expression. While I search for 'man' in tags it returns tags contains 'woman' too; because its substring. SELECT '#hellowomanclothing' REGEXP '^(.)*[^wo]man(.)*$'; # returns 0 correct, it contains 'woman' SELECT '#helloowmanclothing' REGEXP '^(.)*[^wo]man(.)*$'; # returns 0 incorrect, it can...

C# - Can't connect to remote MySQL server

c#,mysql
My problem is that I can't connect to my website remote MySQL server. I have read all answers in stackoverflow.com, but I can't find right answer. Here's my C# code: using System; using System.Collections.Generic; using System.Data; using System.Data.SqlClient; namespace ConsoleApplication3 { class Program { static void Main(string[] args) { SqlConnection...

mysql detect field value change

mysql
I have found a very similar question with exact same title here: Mysql, Check field value change?, but it is not exactly what I need. DB: MySql, Table: tbl_geodata I have query: SELECT id,timestamp,ver_fw FROM tbl_geodata WHERE imei LIKE '353227026533507' Result is: --Full data +------+---------------------+--------+ | id | timestamp |...

Having two arrays in variable php

php,mysql,arrays,variables,multidimensional-array
i've this code, i'm trying to explode my date e.g "04 June 2015" to have the day and the month in other 2 variables, but i have all in a method, with an array, is it possible, my code doesn't work, it writes "Array[0]" why? while ($i < $number) {...

Comparing two values in the same row and change if needed

php,mysql,loops
Im trying to compare two values in the same record and if $minvoorraad > $voorraad , set $voorraad = $minvooraad. Database(cropped picture): This is what i have now but it changes all numbers to same <?php session_start(); include 'connect.php'; $sql = "SELECT Minvoorraad, Voorraad FROM product"; $query = mysqli_query($con, $sql);...

Symfony2 creating and persisting entity relationships

php,mysql,symfony2,doctrine2
I have two entities Skin and Email. I want Email to be a part of the Skin entity, however I can't use the console to update schema automatically right now, and that is probably why I can't get the relationship to work. So I want to store all the Emails...

PHP/MySQL only updates if the variable is less than a few characters

php,mysql
I've got an update query running so that events in the database can be updated. For example, the event record table : Now, when I want to edit the record, I import all the current data from one and show it on a webpage, so that the user can edit...

PHP: While loop not working after adjusting SELECT for SQL injection prevention

php,mysql,select,sql-injection,associative-array
I am trying to set up PHP queries for MySQL in a way to prevent SQL injection (standard website). I had a couple of INSERT queries where changing this worked well but on the following SELECT I keep getting an error since the update and it looks like the while...

Displaying MySQL results in a single table

php,mysql,database
I created an interface in which the user can choose which results are going to be displayed. The options selected by the user are saved in an array, so the MySQL query goes through the different elements of this array. So I want to display the results in a single...

Awarding points to an applicant only once:-no duplicate

php,mysql
I have my online job shortlisting system where administrators give points to applicants on basis of their competence.I dont want an admin to vote an applicant more than once but he can vote more than once only if it is on a different applicant.how do i go about it? my...

Ignore Group if LIMIT is not reached in MySQL

mysql
I am working on a rather tricky SQL for my level of knowledge. I have searched and searched for an answer but haven't came across anything. Hopefully someone can shed some light on this. How can you stop SQL from outputting group of rows if the limit set is not...

How do I find records in one table that do exist in another but based on a date?

mysql,sql
I want to find all the dealers who haven't had an order in 2015 yet. I know this query doesn't work but I thought it might be helpful to understand what I want to do. In this example I want to get just "Bob" as a result. He is the...

Entering data into mysql database using php

php,mysql,forms,mamp
Customer will complete a form and enter a pathway where they will want the CSV to be exported to. The pathway is entered using the top section of the php (below): <form action="register.php" method="post"> Enter file pathway where CSV will be saved: <input type="text" name="username" required="required"/> <br/> <input type="submit" value="Enter"/>...

I need help using a class in a function in PHP

php,mysql,pdo,mysqli
I'm trying to convert all my mysqli over to PDO, but I'm pretty green with PDO and my problem might be elementary. I'm having trouble with the first query I'm switching over. Here is my setup. In my login.php, I call a function like so: login.php $user = attempt_login($username, $password);...

C# MySQL Parameters.AddWithValue

c#,mysql
Hello I found this code snippet for Adding Values to MySQL Commands in C# MySqlCommand command = connection.CreateCommand(); command.CommandText = "INSERT INTO tb_mitarbeiter (Vorname) VALUES (?name)"; command.Parameters.AddWithValue("?name", mitarbeiter); connection.Open(); command.ExecuteNonQuery(); Now I want to add data to more than one coloumn, but if i try it like this: command.Parameters.AddWithValue("?id", Projektid,...

Is it possible to connect to remote DB while working in localhost?

php,mysql,git,phpmyadmin
I am working on a project with my developers on localhost. We are using git to collaborate but are having issues collaborating the database using git or using a remote db. Is there any possible way where I can use git or set up a remote server while working with...

How to create multiple jquery form fields and insert in mysql database, without using mysql_real_escape_string

javascript,php,jquery,mysql
I need to create dynamic text fields with a button (which simply adds a new row of dynamic text fields). Then I need to INSERT the data in a in the database. I borrowed an example of what I need done from someone else's question, the problem is, it uses...

How can I create missing date records for each employee based off a limited calendar?

mysql,sql,tsql
I have two tables, one an abbreviated calendar: create table dbo.calendar ( [date] datetime ) insert into dbo.calendar values ('20150101 00:00:00 AM') -- 1/1/15 insert into dbo.calendar values ('20150102 00:00:00 AM') -- 1/2/15 insert into dbo.calendar values ('20150103 00:00:00 AM') -- 1/3/15 insert into dbo.calendar values ('20150104 00:00:00 AM') --...

concatenate field names in a mysql update with inner join

php,mysql
Concatenating two field names from different tables using INNER JOIN during UPDATE statement. I am trying to concatenate two field names in a mysql update statement. This is what I have so far, needless to say it isn't working, any suggestions would be much appreciated. UPDATE products INNER JOIN sub_categories...

MySQL - How can I know my query is tuned?

mysql,performance,explain
I'm creating a DB from scratch and I'm trying to create queries with performance in mind. For test purpose I filled my tables with test data. My query needs to join 3 tables: 2 with millions of rows and the third with hundred thousands rows. How can I know my...

Changing values after MySql DB Collection

php,mysql
I currently collect data from a mysql table then post the data using Curl. The values are : title : surname : email Max Test [email protected] How do i collect the data then change the feild names for post to : MainTitle : Lastname : MainEmail Max Test [email protected] this...

Notice: Array to string conversion in “path of php file” on line 64

php,mysql,arrays,oracle
PHP CODE -: <!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> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <title>Untitled Document</title> </head> <body> <?php $rows =0; $fp = fopen("leave1.csv","r"); if($fp){ while(!feof($fp)){ $content = fgets($fp); if($content) $rows++; } } fclose($fp); //echo $rows; $_SESSION['rows'] = $rows; ?>...

Select all none null values from all columns in a table

mysql,sql
I am having an issue going through column by column and getting a count of all the non-null values in each column. I think issue is how I am getting the column names. In the last select statement where I am trying to select all of the values in each...

How to post time value in database in PHP

php,mysql
I was trying to post date value inside my database but every time I try, the date is not coming, but the auto row is working. Here is my PHP code: <?PHP mysql_connect("localhost","root","") or die("Could not connect: " . mysql_error()); mysql_select_db("test") or die("Couldn't connect db!"); $times = $_POST['times']; $id_time =...

Temporarily Number Rows to Group by in MYSQL

mysql
I am trying to create a temporary column that gives each set of five results a number so that I can group by.. I am having a lot of trouble trying to work out how to do this. I don't want to take any criteria into account, I want the...

SQL result table, match in second table SET type

mysql,sql
The following two tables are not liked by any type of constraint. First i have a table called subscription_plans that looks like this: name | price | ID ------------------- plan_A | 9.99 | 1 Plan_B | 19.99 | 2 plan_C | 29.99 | 3 I have a second table called...

MySQL trigger help, capturing a variable from php insert into

php,mysql,triggers
I am writing a MySQL trigger to encrypt a column of sensitive data using RSA plugin. There is an insert statement from the website which is PHP based $sql = "insert into redcap_data (project_id, event_id, record, field_name, value) values ($project_id, " . getEventId() . ", '" . prep($_GET['id']) . "',...

MySQL: Select several rows based on several keys on a given column

mysql,sql,database
I have this mysql table structure: ------------------------------------ | item_id | meta_key | meta_value | ------------------------------------ 159 category Bungalow 159 location Lagos 159 price 45000 160 category Bungalow 160 location Abuja 160 price 53500 ... 350 category Bungalow 350 location Lagos 350 price 32000 What I'd like to do is select...

php form get parameters from url and store it until the final page

php,mysql,forms,get
I wrote a simple PHP survey application about movies and I am using one of the crowdsourcing services to collect data. In order to verify and rate workers, I have to generate VCODE (https://microworkers.com/vcode.php) in the final page and so workers can submit it as their proof of finishing my...

Using VLOOKUP formula or other function to compare two columns

mysql,excel,vba,date
I have one table like this: SHORT TERM BORROWING 1/6/2009 94304 12/31/2010 177823 6/30/2011 84188 12/31/2011 232144 6/30/2012 94467 9/30/2012 91445 12/31/2012 128523 3/31/2013 83731 6/30/2013 78330 9/30/2013 70936 12/31/2013 104020 3/31/2014 62345 6/30/2014 62167 9/30/2014 63494 12/31/2014 104239 3/31/2015 69056 I have another column which lists each date from...

If I export my database with phpmyadmin will it lock my tables or take my database down?

mysql,database,phpmyadmin
I want to create a backup of my database using the phpmyadmin export function. the database can't have any down time so I need to know if running a database export will take the database down? I've looked on-line but all I get is instructions on how to export. No...

how will i order this query by DoctorName

php,mysql
I use this query but I don't know how will I order this by DoctorName? $q=mysqli_query($link,"Select * from doctor where status='". $stat ."'"); ...

how to get more than one max records in MySQL

mysql,max
This is the answers that I want to get.But if I used MAX() function in MySQL, it just return one record.How to handle it? A_plus ID 2 12345 2 45678 As decribed above,The SQL I used as fellow,but it just return one record. SELECT MAX(A_plus_Num) AS A_plus, ID FROM (SELECT...

MYSQL, PHP, PDO Check if row exists [on hold]

php,mysql,pdo
Im trying to check to see if value stored in $steamId is already in the table but its not working. What am i doing wrong. Account_info table +------------+---------+----------+-----+ | Steam_id | Partner | Token | RID | +------------+---------+----------+-----+ | Steamid222 | Part222 | token222 | 1 | +------------+---------+----------+-----+ $stmt =...

Select customers without address

mysql
There are two tables ps_customer and ps_address. ps_customer contains: id_customer and customer.email ps_address contains: id_customer and few other fields. I want to select customers who's data is not present in ps_address table. I'm using Sql Query: SELECT id_customer, customer.email FROM ps_customer WHERE id_customer NOT IN SELECT id_customer FROM ps_address; But...

Uploading PNG to Website Server

php,mysql,image,png
I have seen loads of other topics and I've tried them all. Can someone please help with why this script won't upload PNG files? Blank PNG image being displayed. $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 = imagecreatefrompng($image); imagealphablending($source_image,...

Unable to connect to your database server using the provided settings in Codeigniter

mysql,.htaccess,codeigniter
Actually I'm trying to configure my codeigniter live file to localhost. I have, created a database called test_bs then imported the tables Then change the base_url like $config['base_url']=http://localhost/gu_ci/'; and $config['index_page']='index.php'; in config.php And then did a change on database.php,Which contains $active_group = 'default'; $active_record = TRUE; $db['default']['hostname'] = 'localhost'; $db['default']['username']...

Can Rails deal with DB uniqueness without index?

mysql,ruby-on-rails,rdbms
I see add_index ~ unique: true statement in schema.rband think uniqueness is constraint for table, not for index.Using index is one of way realizing uniqueness, Programmer should not designate to the RDBMS "how" and index quicken searching but take costs inserting. In fact, is there another way to keep uniqueness...

Function/Trigger already in use?

php,html,mysql,mysqli
Im having problems getting an update function to work. The function marks badges as seen so that they are hidden from a notification window. The function is called when the user clicks a button to mark them as seen. I have two triggers on the table its trying to update...

MySQL Query returning strange values

php,mysql
The query is supposed to do the following: Obtain the question and associated answers based on the identification number of the question. (In this case the identification number is called account_id. Order them so that each question (and it's appropriate answers) are lined up in order. The query: SELECT *...

Read db and write to file in python

python,mysql
I have a connection to mysql db and making queries with that but I need them write to file immediately, what is the easy way of do this ? conn = MySQLdb.connect(host="localhost",user="root",passwd="",db="mydb") cursor = conn.cursor(MySQLdb.cursors.DictCursor) cursor.execute("SELECT * FROM books") .....

how to get records from field having contain field ids like (1,2,3) in a field in mysql?

php,mysql
I have 2 tables students and ex_students (1) Table name: students +----+--------+ | id | name | +----+--------+ | 1 | Alex | | 2 | Bill | | 3 | Cath | | 4 | Dale | | 5 | Evan | +----+--------+ Other table of ex_students (2) Table...

creating stored procedure in mysql calculate profit from product table

mysql,sql,database,stored-procedures
My following code show some error: 1064 - 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 'BEGIN SET @v1:= (select sum( cost_price * current_stock) from product)' at line 2 CREATE PROCEDURE sp_profit BEGIN...

How do I display my mysql table column headers in my php/html output?

php,html,mysql,table,data
2 Questions... Scenario: I would like to query my database table via a form and then display the results that occur(if there are results) and my current situation is that it does work but it clears the form completely and leaves my to an empty page with just the results...

IBM Cognos _days_between function not working

mysql,database,date,cognos
I'm having an issue with Cognos 10. I'm trying to calculate the number of days between to dates so I use the _days_between( date1, date2 ) function. _days_between ([Derniere Date Changement diaphragme] , [Premiere Date Changement diaphragme]) I'm quite sure the two Dates are Date objects ( I set them...

Fail to create array with query values

php,mysql,arrays,pdo
I try to save the query values into an array, but it wont become filled. The query should give something out because any values in the database accomplish the terms and the cookies have too a value. Where is my mistake? Output: Array ( ) $range = 'range'; $_COOKIE["$range"]; $longitude...

MySQL 4 Table Join

mysql,table,join
I've been trying to get the proper data for this 4-table join for awhile and I can't seem to pin it down. I'm trying to grab a list of ids from songs based on who I am following in follows, and show the song id if my followers are the...

How to check if data already exists then randomly generate new data from an Array

php,mysql,arrays,mysqli
Okay so, I made an array containing 270+ different strings. The main goal is too echo out 60 strings from that array and combine them.. Ex. 1.String, 1.String2. The 2 echo'd out strings are a combination, and should be together like 1-1 and 2-2, 3-3, and ect. In total there...

WHERE If condition is not met get all values including NULL

mysql
Either get only those userIDs with the specified @Lastname or if @LastName = 'All' then get all userIDs even if they have NULL values as their LastName. SELECT userID INTO #table FROM users WHERE LastName = CASE WHEN @LastName = 'All' THEN LastName ELSE @LastName END The above query only...

PHP / MySQLi: How to prevent SQL injection on INSERT (code partially working)

php,mysql,mysqli,sql-injection,sql-insert
I am new to PHP and hope someone can help me with this. I would like to store two values (an email and a password) in a MySQL db using PHP. The input is passed to the PHP page via Ajax in jQuery (through an onclick event on a website)....

Group by date mysql and codeigniter

php,mysql,codeigniter
I have table like this: date_time value 2015-06-20 10:00:00 10 2015-06-20 9:00:00 6 2015-06-21 6:00:00 5 2015-06-22 11:00:00 12 2015-06-22 10:00:00 11 2015-06-23 4:00:00 20 I want to get the total value of each date Currently I have this on CodeIgniter but is not working: $this->db->group_by('date_time'); $query = $this->db->get('table_a'); Any...

Show tables with LIKE and NOT REGEXP

mysql
I would like to get all tables in a database which start with "wp_" but not with "wp_X" (where X is a digit) SHOW TABLES FROM database WHERE Tables_in_database LIKE 'wp_%' AND Tables_in_database NOT REGEXP 'wp_[0-9]+%' gives me all including wp_2*, wp_3*,......

How to show Only a selected Number of Database entries on the page [on hold]

php,html,mysql
The question is, if I have got 100 entries in my Database and want them all getting displayed on my page, how can I only show a selected Number like it's the case in webshops etc. There you've got 37 products e.g. and after 10 products you have to select...

select: result based on occurrence of explicit value

mysql,sql
Given is following mysql table: CREATE TABLE fonts (`id` int, `fontName` varchar(22), `price` int,`reducedPrice` int,`weight` int) ; INSERT INTO fonts (`id`, `fontName`, `price`,`reducedprice`,`weight`) VALUES (1, 'regular', 50,30,1), (2, 'regular-italic', 50,20,1), (3, 'medium', 60,30,2), (4, 'medium-italic', 50,30,2), (5, 'bold', 50,30,3), (6, 'bold-italic', 50,30,3), (7, 'bold-condensed', 50,30,3), (8, 'super', 50,30,4) ; As...

Trying to rewrite mysql_* to pdo

php,mysql,pdo
First of all I should say that I started to learn PDO and trying to rewrite some old mine codes. Here is one that I found on tutorial for a jquery/ajax/pdo/mysql comment system which is working. This is the mysql_* part which I'm trying to rewrite -> file submit.php which...

Escape password in mysqldump command

mysql,passwords,mysqldump
I am using this; mysqldump -u userabc -pabc123 dbname | gzip > /var/backups/archives/mysql/dbname_$(date +\%d-\%m-\%Y_\%T).sql.gz This works but if the password contains a ^ for example it fails, how can I escape this character and still have mysqldump work with the -p flag; mysqldump -u userabc -pabc^123 dbname | gzip >...

php mysql select insert on different DBs in same server

php,mysql,insert-into
I have two DBs on the same server. Table1 in db1 has 11 columns, and around 8 million rows of data. I want to insert four columns' worth of table1 data (i.e., 4 columns x 8 million rows) into table2 in db2. I tried this using php in the script...

Scrambling(randomize) row id's in mysql table

php,mysql,sql
I have table images with ID's starting from 1 to 5000. On the page they are showed straight from 1 to 5000. What I wonder is it possible to scramble the records inside database table not to query them by RAND() on the page because they will be random on...

Getting total for Invoiced Paid and the amount owing for each client

mysql
I have tried many of the scripts that I found here but none did what I required. I have 3 tables: clients, invoiced and payments. I would like to: sum all invoices per client within a date range sum all payments for clients with in the date range then subtract...

SQL Group By multiple categories

php,mysql,sql,mysqli
This is the query I'm using to count how many events are booked for each category of bookings. SELECT Categories.name, count(case when locations.name ='loc 1' then 1 end) as Location1, count(case when locations.name ='loc 2' then 1 end) as Location2, count(case when locations.name ='loc 3' then 1 end) as Location3,...

Order by count not sorting the records correctly?

php,mysql,database
I am using MySQL. I have two table i have attached the images of the both tables? When i apply ORDER by Count then the query retrieving result's from database but not sorting them correctly, i don't know what wrong i am doing? i searched some similar question in stack...

I Want to fetch SQL Records in MySQL of current Year

mysql
I am using the query SELECT count(enq.`enquiryId`), Month(enq.`date`), Year(enq.`date`) FROM enquiry enq GROUP BY MONTH(enq.`date`) But I get all year records. I just want to get Current or any input year(At Runtime) records. Please help....

Get the record with the min distance

mysql
I am trying to get the record with the min distance with this query below but I am no getting anything back. The query is being executed but the result is empty. How can I get the record with the mini distance? query: SELECT mac, stop_name, stop_distance, speed FROM behaviour...

Group and order by a column but donot include that column in results

mysql,sql
I've been trying to figure out how I can modify this query so that the result set does not include the numHits. I want the same results in the same order, just not have the numHits included. SELECT `newel_inventoryKeywordIdDictionaryId`.`inventoryId` ,COUNT(`newel_inventoryKeywordIdDictionaryId`.`inventoryId`) as numHits FROM `newel_inventoryKeywordIdDictionaryId` , `newel_inventoryDictionary` WHERE `newel_inventoryKeywordIdDictionaryId`.`dicId` = `newel_inventoryDictionary`.`dicId`...

Query how often an event occurred at a given time

mysql,sql
[Aim] We would like to find out how often an event "A" ocurred before time "X". More concretely, given the dataset below we want to find out the count of the prior purchases. [Context] DMBS: MySQL 5.6 We have following dataset: user | date 1 | 2015-06-01 17:00:00 2 |...

MySQL group_concat() function with join operation

mysql
I have following tables: CREATE TABLE school( schoolId VARCHAR(36), sname VARCHAR(64) PRIMARY KEY(schoolId) ); CREATE TABLE school_building( schoolId VARCHAR(36), bname VARCHAR(64), PRIMARY KEY(schoolId, bname), FOREIGN KEY (schoolId) REFERENCES school(schoolId) ); CREATE TABLE school_faculty( schoolId VARCHAR(36), fname VARCHAR(64), PRIMARY KEY(schoolId, fname), FOREIGN KEY (schoolId) REFERENCES school(schoolId) ); I want to write...

Loop through database table and compare user input

mysql,c
I am trying to loop through the rows in a MySql table and compare the data in a certain column to some user input using C. Currently my code looks like this: MYSQL *cxn = mysql_init(NULL); MYSQL_RES *result; unsigned int num_fields; unsigned int num_rows; char *query_string; MYSQL_ROW *row; if (mysql_real_connect(cxn,...

Geolocation to find nearest place [on hold]

php,html,mysql,geolocation
I have a database of national park names (Alcatraz Island, Death Valley, Biscayne, etc.) which all have coordinates. What's the simplest way for me to match the closest park in my database to their current location whether on a mobile device or on a desktop?...

how to convert the text outputted from a database to links?

php,mysql
Iam class 11th..recently I started learning php and mysqliI have been facing a problem. I am trying to create a database which has a small list of movies.when the page loads, it displays the list of those movies from database..but the problem is, it displays them as a simple text..i...

Syntax error in Insert query in Mysql in VB.Net

mysql,vb.net
I am doing project in VB.NET and backend is mysql Can you please tell me where the error is occured Public Sub ins() con.Open() 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...

MySQLdb Python - Still getting error when using CREATE TABLE IF NOT EXISTS

python,mysql
I'm using this code to create a database with tables in Python: def CreateDatabase(): global DB_CNX global DB_NAME cursor = DB_CNX.cursor() cursor.execute("""CREATE DATABASE IF NOT EXISTS {} DEFAULT CHARACTER SET 'utf8'""".format(DB_NAME)) cursor.execute("""CREATE TABLE IF NOT EXISTS NAMES(NAME VARCHAR(50) PRIMARY KEY NOT NULL)""") DB_CNX.close() But even if I use the syntax...

Retrieve Values As Column

mysql,sql
I've the following table in my PHPMYADMIN The desired output is headline                              impressions              clicks Buy A new Iphone                 ...

Error when trying to install app with mysql2 gem

mysql,ruby-on-rails,ruby,ruby-on-rails-4
Im trying to install an open source rails 3.2.21 application that uses the mysql2 gem, but when i try and run the bundle commant I get the following error: Fetching: mysql2-0.3.18.gem (100%) Building native extensions. This could take a while... p ERROR: Error installing mysql2: ERROR: Failed to build gem...

How to get entries from SQL that only appear in certain value?

mysql,sql
I need to find entries in my SQL table that always and only appear in a certain value. For example: DeviceID Transmission -------- ------------ 000329 Inventory 000980 Inventory 004406 Starting 000980 Stopping 000329 Inventory 004406 Inventory Now I need to find all DeviceIDs that only have Inventory Transmissions and never...

How to search images by name inside a folder?

php,mysql,image
I have a MySQL table with a column "rounds" and each "rounds" has his own photos. Exemple round1 has photos from start=380 end=385. This means it has 6 photos and the name of the photos contains 380,381,382,383,384 or 385 inside. I use this PHP code to display the photos from...

Select most recent entry where timestamp is from the past hour

mysql
I've tried multiple ways, all of them resulting in incorrect rows returned, so far I've tried SELECT * FROM table WHERE id=1 AND timestamp >= NOW() - INTERVAL 1 HOUR ORDER BY timestamp DESC LIMIT 1 .... AND timestamp > DATE_SUB(NOW(), INTERVAL 1 HOUR) ORDER BY timestamp DESC LIMIT 1...

Improving SQL Queries

php,mysql,sql-server
I have recently designed a website that used a lot of queries. During the time I was developing my website I came across an issues which was very time consuming and frustrating. So the problem was that at a certain point I wanted to add an additional feature to the...

Error 1136: Column dosent match value count at row 1

java,mysql,sql
I've tried to add this row at the DB, but i've the problem: Error 1136: Column dosent match value count at row 1 the table is : Table: cliente Columns: CFCL varchar(16) PK CognomeCL varchar(30) NomeCL varchar(30) SessoCL varchar(1) ComuneNascitaCL varchar(20) DataNCL date IndirizzoCL varchar(30) TelefonoCL varchar(20) CittadinanzaCL varchar(30) PatenteCL...

Unique movies by UNION the results of two join, MYSQL

mysql,join,union
I ran the following query to UNION the result of two joins over the below 4 tables: 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 JOIN...

UPDATE SET = SELECT FROM

mysql
I am trying to update table based on a select query using this: UPDATE branches SET name = (SELECT CONCAT(comp.name," ",bra.subsurb) as newname FROM companies comp RIGHT JOIN branches bra ON comp.id = bra.company_id) Which, according to this question, should work but this produces an error: You can't specify target...

Convert March 2015 to 03/2015 and make into date

mysql,date
I have a column which holds values in the format 'June 2015', 'July 2015', 'March 2014', 'February 2016'. I need to convert these into the format 06/2015, 07/2015, 03/2014, 02/2016.. but also make them a date so I can sort by this column. I currently have the following select: SELECT...

Purging Database - Count purged/not-purged tables

mysql,sql,sql-server,database,stored-procedures
So I am working with a database where I will be purging various tables that contain rows that are older than 30days. I have fairly limited knowledge of SQL and wanted to know if there was a certain way to select the row that will be purged and the rows...

MySQL Select where count

mysql
I have the following MySQL query: SELECT l.user_id, u.ID, u.ULN, count(u.ULN) As ULN_count FROM table_Learning l LEFT JOIN table_users u ON l.ULN = u.ULN WHERE l.user_id is null AND (SELECT COUNT(ULN) FROM Table_users WHERE ULN = u.ULN GROUP BY ULN) < 2 GROUP BY u.ULN What I would expect from...

Fatal error in if stament [duplicate]

php,mysql,mysqli
This question already has an answer here: Compile Error: Cannot use isset() on the result of an expression 2 answers Error - Cannot use isset() on the result of an expression (you can use "null !== expression" instead) I'm getting the error above the start of the if statement...

MySQLSyntaxErrorException in SQL syntax

java,mysql,jsp,mysql-connector
I am trying to select data from a table using prepared statement. But it seems like I am getting syntax error which I cannot solve alone. try { Class.forName("com.mysql.jdbc.Driver"); String url = "jdbc:mysql://localhost:3306/mydb"; String dbusername = "root"; String dbpassword = ""; // Change it to your Password // Setup the...

compare today's date with unix timestamp value in database

php,mysql
In database I am storing date value unix timestamp value for e.g.'1434952110' using time() now I am trying to compare todays value with this value as below $jobpostdate = date("Y-m-d");//today's date passing in database to compare query $sql = "SELECT jsp_title, jsp_subtitle, jsp_desc, jsp_location, jsp_date "; $sql .= "FROM js_projects...

timestamp SQL to Excel

php,mysql,sql,excel
If this is a duplicate, please let me know, I haven't found anything. I have written a php file that can read content from a database table and write it into a excel .xls file. Everything works fine except by that timestamps. In my generated .xls file every timestamp is...

Mysql Join - Filter results based on two max values

mysql
I am having the following data set: +----+------------+------+-------+----------+ | id | date | week | time | price | +----+------------+------+-------+----------+ | 1 | 2015-05-01 | 24 | 13:25 | 1.251325 | | 2 | 2015-05-01 | 24 | 14:25 | 1.251256 | | 3 | 2015-05-02 | 24 | 15:25...

How to call MySQL view in Struts2 or Hibernate

java,mysql,hibernate,java-ee,struts2
I have a view in MySQL database CREATE VIEW CustInfo AS SELECT a.custName, a.custMobile, b.profession, b.companyName, b.annualIncome FROM customer a INNER JOIN cust_proffessional_info b ON a.cust_id=b.cust_id Is there any way that i can call this view using Struts2 or in Hibernate. I have tried to search it but could not...

SQL only one cell from column can have X value

mysql,sql
Is there a way to ensure, when you design a table - SQL, MySQL -, that only one cell from column X is true, the rest being automatically false? When you change the value of a different cell to true, all other cells should automatically become false. A radio button...

Using Sum in If in Mysql

mysql,sql,select,sum
I want to select the sum of values of my_field. The unit of my_field is in seconds. I don't want to exceed 24 hours, so when it exceeds 24 hours, I want it to select 24*60*60 = 86400. SELECT IF(SUM(my_field) > 86400, 86400, SUM(my_field)) ... This solution doesn't seem to...

Can't find FULLTEXT index while using IN BOOLEAN MODE

mysql,sql,full-text-search,mariadb,match-against
I'm trying to filter some data with an MATCH ... AGAINST WHERE clause. But I get the following error: Can't find FULLTEXT index matching the column list This is an query I execute: SELECT PAR.idPartij, PAR.Partijnaam, PRO.Productnaam, PER.Perceel, R.Ras, PAR.TagNR FROM Partij AS PAR LEFT OUTER JOIN Product AS PRO...

How to program a recurring billing/invoice system using PHP and MySQL

php,mysql
I have already programmed a basic invoicing system using PHP/MySQL which has a table structure as follows; Invoice table; invoice_id, invoice_date, customer_id, etc Invoice line table; invoice_line_id, invoice_id, quantity, price, description, etc I need the system to automatically generate future invoices at set intervals (for example every 1 week or...

Get the last record even when the table contains one record

java,mysql,jdbc
I am trying to get the last record in my table with prepared statement. I have tried both quieries below but I am facing problem to get it when the table just contains one record in it. I am getting result when I execute the query alone but when I...

Use SQL output 1 by 1

php,mysql
I have made a little system where SQL gets the first 5 games based on ranking. Now I want to use them freely with PHP. So the best way to do that imo is to give them each a value $one = first result $two = second result etc. but...

MySQL Case When Statement

mysql
How do I strip results with null (or zero) results when using GROUP BY and CASE WHEN logic? a hypothetical example is: SELECT City, SUM(case when name2015 = 'Jane' then income2015 end) income2015_total, SUM(case when name2020 = 'Jane' then income2020 end) income2020_total from finaldata GROUP BY City with a database...

php foreach loop insert data feed back repeat everytime

php,mysql
this is my php code. hire is a problem i use check box selection and insert data in my database. but it insert data by foreach loop. so when press ok if data successfully insert then for 3 selection it say successfully register successfully register successfully register but i want...

Inner Join 3 tables pl/sql

mysql,sql
Basically i want to inner join 3 tables and the case is understood by the query itself as follows. Tables: A has 2 columns column 1 and column 2 B has 2 columns column 3 and column 4 C has 3 columns column 5,column 6 and column 7 Query: select...

phpmyadmin collation: latin and cyrillic

mysql,character-encoding,collation
Could someone tell me please how to configure MySQL DB in phpmyadmin for storing both latin and cyrillic data sets in the same table? Thanks!

Php Mysql Query not working properly

php,mysql
I have a table name tblnetworkstatus and I have 11 columns Id issue_name affected_server affected_service issue_type priority duration status start_date end_date description I am getting id in affected_server and affected_service which I am storing in my DB, now I have three situations Either both affected_server and affected_service has been selected...