I'm struggling with writing an SQL query to display the leaf node (last node) along with their parents names without having to use a procedural function in php (I need to do this in the SQL) and I'm wondering if someone can help me. Im using MySQL as the db. I'm using the adjacency list model for storing the data. What I want returned...
I'm trying to list the number of records per hour inserted into a database for the last 24 hours. Each row displays the records inserted that hour, as well as how many hours ago it was. Here's my query now: SELECT COUNT(*), FLOOR( TIME_TO_SEC( TIMEDIFF( NOW(), time)) / 3600 ) FROM `records` WHERE time > DATE_SUB(NOW(), INTERVAL 24 HOU...
Hi guys. I'm looking to create an options table in my db that makes every record a system option, so I can work with a little number of fields. My db has the following structure: 3 columns named id, name, and value The following data is inserted as an example: +--+-----------+--------------------------+ |id|name |value ...
I'm attempting to create a true mosaic application. At the moment I have one mosaic image, ie the one the mosaic is based on and about 4000 images from my iPhoto library that act as the image library. I have already done my research and analysed the mosaic image. I've converted it into 64x64 slices each of 8 pixels. I've calculated the a...
I need to store phone numbers starting with 0 but whenever i try to store this in MySql table the starting ZERO is removed because no number start with Zero actually how i can solve this problem please do i need to change the field type from Integer to another type please help ...
In the below SP i will be getting result in One single column. How can i name the column of the output ? DELIMITER $$ DROP PROCEDURE IF EXISTS `InsGen` $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `InsGen` ( in_db varchar(20), in_table varchar(20), in_ColumnName varchar(20), in_ColumnValue varchar(20) ) BEGIN declare Whrs varcha...
I made simple procedure just to demonstrate CREATE PROCEDURE `demo`(demo_int int) BEGIN DECLARE minid INT; SELECT min(id) FROM (SELECT id FROM events LIMIT demo_int,9999999999999999) as hoo INTO minid; END$$ The problem is with demo_int, if i change it to LIMIT 1,9999999999999999 it works but LIMIT demo_int,9999999999999999 D...
I need to combine the results for two select queries from two view tables, from which I am performing calculations. Perhaps there is an easier way to perform a query using if...else - any pointers? Essentially I need to divide everything by 'ar.time_ratio' under the condition in sql query 1, and ignore that for query 2. SELECT g...
iam having 2 tables table Items Table (this table holds all items iam having) itemId --------- Item1 Item2 Item3 Item4 Item5 table 2 users_item relation UserId || ItemId 1 || Item1 1 || Item2 userId one has stored 2 items Item1,Item2. Now i want to write a query on table1 (Items table) so that it displays all items which user1 h...
An instance of WordPress that I manage goes down about once a day due to this monster MySQL query taking far too long: SELECT SQL_CALC_FOUND_ROWS distinct wp_posts.* FROM wp_posts LEFT JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) LEFT JOIN wp_term_taxonomy ON wp_term_taxonomy.term_taxonomy_id = wp_term...
Hey everyone, I am trying to figure out a way to query my mysql server so that even if a company does not have anything posted for the day and the user clicks on their logo, it still adds them to the list. That sounds a little confusing so let me try to explain it another way. Say I have 3 companies in my database: Comp1 Comp2 Comp3 ...
iam having an array of items like [item1,itmem2,item3]; i have to insert these items at a particular userId: final results look like this UserId ItemId 2 || item1 2 || item2 2 || item3 currently iam looping through the array in php code and inserting each item one by one eg foreach($items as $item) { insert into items (...
I have a column using IDs that I want to sort by. It has ID values from 1 to 3. However, instead of just using ASC of DESC, I want to do a custom sort by 2, 3, 1. How do I make this happen? ...
Hi there I need to create something like this: select name from table where active = 1 AND (name LIKE 'bla' OR description LIKE 'bla') The first part is easy: $sqlcmd = $db->select() ->from("table", "name") ->where("active = ?", 1) Now comes the tricky part. How can I nest? I know that I can just write ->orWhere("name LIKE ? OR ...
I have a table defined like this: create table users ( id int(10), age int(3), name varchar (50) ) I want to use a query to update just age, which is an integer, that comes from an html form. When it arrives to the method that updates it, it comes as a string, so I change it to integer with PHP and try to update the table, but ...
If it matters, I'm using Firebird 2.1 database. I have three tables, one with keywords, one with negative keywords, and the other with required keywords. I need to be able to filter the data so the output has just the keywords that meat the stipulation of not being in the negative keyword list, and IF there are any required words, th...
Hi, I want to do an average : the problem is for 1 item i'm calculating the AVG of each elements (working) but as soon as i want the GLOBAL average of the averages of the categories (something and foo) it doesn't work (mysql throw me an error : see the syntax i used just below). I need to do that because i want to sort the result by th...
I am using the below SP to return the value of Generated Insert statement and it works fine when executed in Query browser. When i try to get the value from C#, it give's me "System.Byte[]" as return value. When i try to get the value from MySql query browser, it give's me return value as : 'insert into admindb.accounts values("54321...
How to Check for record existence and do Insert or Update in MYSQL ? I have a script, which has set of Insert statements for multiple tables. Now when i try to execute the Insert statement, i want to do the following: Check for the record existence and then do Insert or Update. If the Record is not exist do Insert. If the Record is a...
i have more than two tables in my database and all of them contains same field names like table A table B table C field1 field1 field1 field2 field2 field2 field3 field3 field3 . . . . . . . . ...