mysql-query

mysql: how can i remove the street number from a streetaddress column?

hi. I have a column named streetaddress that contains <Street Number> <Street Name> for example: 15 rue Gontier-Patin 4968 Hillcrest Circle how can i remove the numbers from the beginning of the row purely in sql? ...

mysql: substring that returns the first occurance from the right ? (subrstring?!)

Hi. is there a way to return the first occurrence of a space from the right side of the string in sql ? thanks! :) ...

Mutiple criteria in 1 query

I have one table of peoples names and I have another table of pledges these people have made. They might pledge money against item 1,2,3 or 4. Each time a pledge is made an entry is made in the pledge table with the id of the person and the pledge number they made. I would like a query that gets a count of distinct people that made pl...

Searching in mysql db with narrow results

Hello, My search query is something like this SELECT files.id,files.file,files.name,files.uid,files.size,files.downloads,files.deleted,files.descr, files.upload_id,files.thumb,files.category,files.expirydate, MATCH(name,descr) AGAINST ('$all $none $any' In BOOLEAN MODE) AS score, users.`user` FROM files Inner Join users O...

sql date order by problem

i have image table, which has 2 or more rows with same date.. now im tring to do order by created_date DESC, which works fine and shows rows same position, but when i change the query and try again, it shows different positions.. and no i dont have any other order by field, so im bit confused on why its doing it and how can i fix it. c...

Including $variable in MySQL

Here are my failed attempts to include a PHP variable in a MySQL expression. Replacing the variable with a 1 results in the results being printed. Any help will be appreciated. $query = " SELECT name FROM teams WHERE id = '$shooterID'"; $shooters = mysql_query($query) or die(mysql_error()); $i = 0; while($shooter = mysql_fetch_arr...

MySQL "in" statement

This question is really just out of curiosity. Can someone shed some light onto why this query: insert into c ( select a.* from a,b where a.x=8 and y=1 and b.z=a.z and active > '2010-01-07 00:00:00' group by a.z ) is a million times faster (or close enough) than the query below whenever there's quite a few records involved...

Select by varchar column with IN() part in condition and int value returns all rows

Hello ! Can someone explain me why with that query: SELECT * FROM `tags` WHERE (tag IN ('willa-lentza', 2016)) it return me all rows from tags table, but when I put 2016 into quotes it works good ? tag column is varchar type. SAMPLE ENVIRONMENT CREATE TABLE `tags` ( `id` int(10) unsigned NOT NULL auto_increment, `tag` varchar...

How to stop a running mysql query

How do I stop a running mysql query programmatically? The problem I'm facing is that a query is constructed using user supplied data, and may occasionally take a very long time to execute (large tables 15 - 30 million rows). I want to offer the user a cancel-option, but don't know how to stop the currently executing mysql-query. The a...

MySQL Update command

I have a query in MySQL UPDATE `mylandho_foreclosure`.`property_commercial` SET `winningBid` = '14000000.00' WHERE `property_commercial`.`propertyId` =325 LIMIT 1 ; but it shows warning Warning: #1264 Out of range value adjusted for column 'winningBid' at row 1 datatype is float(9,2) ...

How do I subract using one single statement in MySQL

How can I subtract the result of this query: SELECT COUNT(Laptops) FROM (SELECT aaaUser.FIRST_NAME AS User,COUNT(workstation.WORKSTATIONNAME) AS Laptops FROM SystemInfo workstation LEFT JOIN Resources resource ON workstation.WORKSTATIONID=resource.RESOURCEID LEFT JOIN ResourceOwner rOwner ON resource.RESOURCEID=rOwner.RESOURCEID LEFT...

Select and count with table relationship?

I have 03 tables with relationship jos_session +userid jos_gj_user +id_user +id_group jos_gj_jb +group_id +category_id I AM WANT TO COUNT user in table jos_session base on category_id in table jos_gj_jb This is what I am trying SELECT COUNT(s.userid) FROM jos_session s WHERE s.user.id IN (SELECT id_user ...

How long does it take to translate SQL Server queries to MySQL?

I have been tasked with estimating the amount of time it will take to rewrite the data access layer of a .NET application from using SQL Server to use MySQL. It is not up for discussion whether this will happen or not...just how long will it take. I have estimated all of the work except for translating the stored procedures in SQL Serv...

MySQL 3 tables in one query?

Hey guys, I'm trying to create a efficient query which will retrieve 'projects' from a database and all the relevant 'tags' which have been assigned to that particular project. My table setup mimics that of wordpress, and is a tad complicated: term - Where I define the tags name, slug etc term_relationships - Links the project to a t...

mysql select query Error

my query throwing Error like select id,cHospital from med_patient where cHospital is not null union select id,cHospital1 from med_patient where cHospital1 is not null union select id,cHospital2 from med_patient where cHospital2 is not null order by 1 Error is ` Notice: Undefined index: cHospital1 in F:\wamp\www\mmr-final\admin\all_h...

Using an Alias in SQL Calculations

Why won't this query work? SELECT 10 AS my_num, my_num*5 AS another_number FROM table In this example, I'm trying to use the my_num alias in other calculations. This results in unknown column "my_num" This is a simplified version of what I am trying to do, but basically I would like to use an alias to make other calculations. My ca...

MySQL LIKE question

I have a script: $friendnotes = mysql_query("SELECT nid,user,subject,message FROM friendnote WHERE tousers LIKE '%$userinfo[username]%' "); And the content in the "tousers" table of the database: Test Example User That script appears to be working well However, if there is a user called "Test2", it would also display content th...

Returning column names from a mySQL table using PHP

Can't figure this out for the life of me. Trying to return the column names from the clients securities table, then return the result as an array. Can anybody point out where I'm getting off track? mysql_select_db("HandlerProject", $con); //Selects database $selectcols = "SELECT * FROM ".$clientname."securitiestable"; //sel...

Select a two-word field name in mySQL, called from PHP

trying to get information from a field and it's not recognizing the field name. $selectsecurityname = mysql_query("SELECT security name FROM securityinfo") or die(mysql_error()); Have a feeling it's because the fields name "security name" is two words. Is there a way to pass a two-word field name, or do I have to change everything to...

How to use mySQL WHERE clause in PHP?

Can't seem to pass anything from the "securityinfo" field. for ($k = 1; $k < $fieldscnt; $k++) { $selectsecurityname = mysql_query("SELECT `security name` FROM securityinfo WHERE symbol = '$symbol[$k]'") or die(mysql_error()); $securityname = mysql_fetch_array($selectsecurityname); $sym = $symbol[$k] echo "<td>$sec...