mysql-query

Insert new row with data computed from other rows

Suppose I have a MySQL table called MyTable, that looks like this: +----+------+-------+ | Id | Type | Value | +----+------+-------+ | 0 | A | 1 | | 0 | B | 1 | | 1 | A | 2 | | 1 | B | 3 | | 2 | A | 5 | | 2 | B | 8 | +----+------+-------+ And, for each Id, I want to insert a new row with...

mysql_query where statment help

I am retrieving values from the url with the GET method and then using a if statement to determine of they are there then query them against the database to only show those items that match them, i get an unknown error with your request. here is my code $province = $_GET['province']; $city = $_GET['city']; if(isset($province) && isset(...

How do I write this MySQL query?

I am working on an Asset DB using a lamp stack. In this example consider the following 5 tables: asset, server, laptop, desktop, software All tables have a primary key of id, which is a unique asset id. Every object has all asset attributes and then depending on type of asset has additional attributes in the corresponding table. If ...

How to write a stored procedure using phpmyadmin and how to use it through php?

I want to create stored procedures through phpmyadmin and later on use it on php. But I dont know how to?. From what I know, I found out that, we cannot manage stored procedures through phpmyadmin. What other tool can manage stored procedure? And I am not sure if it is better option to use stored procedure instead? ...

MySQL different versions other results.

hey, i have 2 version of mysql on windows 5.1.39-community and on linux 5.1.39-log i execute a query: SELECT `o`.`idOffer`, `o`.`offer_date`, `p`.`factory`, `c`.`short` AS `company`, `s`.`name` AS `subcategory`, `ct`.`name` AS `category`, count( (select count(1) from product where idProdu...

Help with MySQL query

I have a table that contains the next columns: ip(varchar 255), index(bigint 20), time(timestamp) each time something is inserted there, the time column gets current timestamp. I want to run a query that returns all the rows that have been added in the last 24 hours. This is what I try to execute: SELECT ip, index FROM users WHERE ip...

Searching a table MySQL & PHP.

I want to be able to search through a MySQL table using values from a search string, from the url and display the results as an XML output. I think I have got the formatting and declaring the variables from the search string down. The issue I have is searching the entire table, I've looked over SO for previous answers, and they all see...

Match two mysql cols on alpha chars (ignoring numbers in same field)

I was wondering if you know of a way I could filter a mysql query to only show the ‘alpha’ characters from a specific field So something like SELECT col1, col2, **alpha_chars_only(col3)** FROM table I am not looking to update only select. I have been looking at some regex but without much luck most of what turned up was searching ...

MySQL AND alternative for each table in a join

I have a simple join in a query however I need to have a condition on both of the tables "confirmed='yes'" but if one of the tables doesn't have any that match the query returns with no rows. Database: .----------parties----------. | id - party_id - confirmed | |---------------------------| | 1 1 yes | | 1 2 ...

Optimize GROUP BY&ORDER BY query

I have a web page where users upload&watch videos. Last week I asked what is the best way to track video views so that I could display the most viewed videos this week (videos from all dates). Now I need some help optimizing a query with which I get the videos from the database. The relevant tables are this: video (~239371 rows) VID(in...

MySQL - Counting rows in preparation for greatest-n-per-group not working?

Referring to SO and other sites have given me examples of how to use MySQL to create a 'greatest-n-per-group' query. My variant on this would be to have a query that returns the first 3 rows of each category. As the basis for this I need to sort my data into a usable sequence and that is where my problems start. Running just the seque...

select random value from each type

I have two tables, rating: +-----------+-----------+-------------+----------+ | rating_id | entity_id | rating_code | position | +-----------+-----------+-------------+----------+ | 1 | 1 | Quality | 0 | | 2 | 1 | Value | 0 | | 3 | 1 | Price | 0 | +----...

Accessing global variables within functions in PHP

There are multiple times in one page where I need to connect and subsequently query a MySQL database, yet my code won't let me. I think it might be something to do with how my files are nested but it makes no sense. I am opening the SQL connection in the header file. The top of the offending page looks like the following: <?php $page_t...

MySQL select help

Hi I have a table that looks like this id : productid : featureid (1, 1, 16) (2, 1, 21) (3, 1, 25) (4, 2, 16) (5, 2, 21) (6, 2, 27) where featureid is a foreign key to another table. I need to select products that have both featureids of 16 and 25, in the example this would be productid 1 but not productid 2. Can someone...

best way to change the date format in mysql SELECT?

Can any one please let me know, i need to change the data format 2010-05-14 17:53 to 14/05/2010 17:53 using mysql select query ...

return multiple row when i have multiple ids in csv format in mysql

Hi all, i know it may be very simple query, but please help me out. i have item id 1,2,3,4 etc.... now i want to get all row regrading that item id what will be mysql query?? i used IN operator but this return only one row SELECT item_id,item_name FROM tbl_item_detail WHERE item_id IN('1,3,4') my table structure is ...

seperated mysql statement query in php

So, I can run the following statements from within mysql itself successfully. SET @fname = 'point1'; SELECT * FROM country WHERE name=@fname;` But when I try to pass the query through php like this and run it, I get an error on the second line $query = "SET @fname = 'point1';"; $query .= "SELECT * FROM country WHERE name=@fname;";...

Searching in several mysql tables

Hello(Здравствуйте) There is a MySQL database with several(but we don't know how much) tables. The only thing I know about them, is that all of them have a prefix 'pref'. So how can I search in every table, if a don't know their names ? Can you help me with the query ? Sorry for my bad english ...

MySQL - SELECT * INTO OUTFILE LOCAL problem.

MySQL is awesome! I am currently involved in a major server migration and previously, our small database used to be hosted on the same server as the client. So we used to do this : SELECT * INTO OUTFILE .... LOAD DATA INFILE .... Now, we moved the database to a different server and SELECT * INTO OUTFILE .... no longer works, understanda...

MYSQL Inserting rows that reference main rows.

I'm transferring my access logs into a database. I've got two tables: urlRequests id : int(10) host : varchar(100) path: varchar(300) unique index (host, path) urlAccesses id : int(10) request : int(10) <-- reference to urlRequests row ip : int(4) query : varchar(300) time : timestamp I need to insert a row into urlAccesses for ever...