mysql

storing temp data for shoping cart

I am doing shopping cart for my website Storing purchase information in session Storing purchase information in table. Which is better solution? If I store it in table how and when will i delete the records in the table (i.e when not purchased by user but in table) ...

Count from a table, but stop counting at a certain number

Is there a way in MySQL to COUNT(*) from a table where if the number is greater than x, it will stop counting there? Basically, I only want to know if the number of records returned from a query is more or less than a particular number. If it's more than that number, I don't really care how many rows there are, if it's less, tell me the ...

MYSQL - Cannot be able to create foreign key

When i execute the below query it throws an error, alter table `ttis`.`users` add constraint `FK_Role_ID` FOREIGN KEY (`ROLE_ID`) REFERENCES `roles` (`ROLE_ID`) Error: Error Code : 1005 Can't create table 'ttis.#sql-9c_8a' (errno: 150) ...

Loop: Need help with loop and efficiency

Currently, I need help with my loop. In each div, I want it to show two sets of FirstName and LastName instead of just one set, but I don't know how can I do that because of the loop. Also, the point of setting different font sizes is to create a visual look which is a funnel like shape. My question is, how can I add another set of name ...

mysql group by performance tuning

select field1,count(*) from table where $condition group by field1 select field2,count(*) from table where $condition group by field2 Basically that's what I'm doing the job now,is there a way to optimize the performance so that MySQL doesn't need to search two times to group by for the where clause? ...

Left Join Issue

Hi, Am trying to do a left join along with a condition. Say this is my table: a b c ------------------------- 1 1 NULL 3 3 something and my query is select * from x left join y on x.a = y.b The problem is that I don't want c to be "something" so when i add select * from x left join y on x.a = y.b where y....

PHP - Getting limited chunks of a large array from MySQL table

Let's say you've got a table like this: ID Cat1 Cat2 1 a red 2 b red 3 c blue 4 d blue 5 e blue 6 f green etc etc etc The goal is to display the ID and Cat1 (unique pairs), split into groups according to Cat2. The easy way out is to run a separate MySQL query for each Cat2, but with a lot...

Gears and Mysql sync

Hi, I am making an offline website that needs to sync every night with a server. Our employees work outside and sometimes underground and need to access the site for its database. But once home they must send us their findings. The first time they connect, I check with google gears if they have the sqlite database and if not I run a sc...

SQL "GROUP BY" issue

Hi, I'm designing a shopping cart. To circumvent the problem of old invoices showing inaccurate pricing after a product's price gets changed, I moved the price field from the Product table into a ProductPrice table that consists of 3 fields, pid, date and price. pid and date form the primary key for the table. Here's an example of what ...

MySQL foreign key problem

A client of mine recently formatted his machine and re-installed MySQL Server and my application. He complained that deleting records from master table is not affecting the child tables. I requested him to send the backup of the database. When I restored the database, I found that the Table Engine has changed to MyISAM whereas they were ...

Count average from another MySQL table

I have 2 tables, one is supplier and one is supplier_feedback - how can I calculate the average rating for each supplier? I currently have this: SELECT s.premium, s.supplier_id, s.name, s.phone, s.website, s.price_low, s.price_high, s.address1, s.address2, s.town, s.county, s.postcode, (SUM( f.rating ) / ( COUNT( f.ratin...

Storing Revisions of Relational Objects in an Efficient Way

I'm not sure if this type of question has been answered before. In my database I have a product table and specifications table. Each product can have multiple specifications. Here I need to store the revisions of each product in database in order to query them later on for history purposes. So I need an efficient way to store the produ...

Return variable from stored procedure MySQL

How can I return a declared string like ( lastInsertId ) from my MySQL stored procedure and out? It's really annoying I can't return error messegts, complate messages and more out to my code in PHP5. I hope somebody can help me here, I have search Google around without luck :( Thanks to everybody. ...

how to Sort out Mysql odbc connection strings stored in registry as plain text

Mysql odbc connection string is stored in the windows registry as plain text. So someone can find it and view my database. How can I sort out this security problem. thanks ...

Troubles with nested queries...

I have this query which works perfectly: SELECT * FROM Customer WHERE SacCode IN ( SELECT SacCode FROM SacCode WHERE ResellerCorporateID = 392 ORDER BY SacCode ) AND CustomerID IN ( SELECT CxID FROM CustAppointments WHERE AppRoomID IN ( SELECT AppRoomID FROM ClinicRooms WHERE ClinID IN ...

How do I find the difference between a datetime type and the current date/time in php?

I have a DATETIME for an entry in my database that is upcoming. I want to know the difference in time between the the DATETIME and the current date/time in Days, Hours, Minutes, and Seconds. I thought I might be able to use the date function to do this, but perhaps I am wrong. This was my approach: $now = mktime(0, 0, 0, date("m"), dat...

c++ Mysql C API Connection Question

I'm building an application which uses Mysql, I was wondering what would be the best way to manage the connection to the actual Mysql server? I'm still in the design phase, but currently I have it Connecting (or aborting if error) before every query and disconnecting after which is just for testing as right now I'm only running 1 query ...

Using JOINS in MySQL

I have this query which works perfectly: SELECT * FROM Customer WHERE SacCode IN ( SELECT SacCode FROM SacCode WHERE ResellerCorporateID = 392 ORDER BY SacCode ) AND CustomerID IN ( SELECT CxID FROM CustAppointments WHERE AppRoomID IN ( SELECT AppRoomID FROM ClinicRooms WHERE ClinI...

MySQL Server Settings

I have a mysql server that gets a lot of users logging in at once. I know in windows you can change the settings so the amount of people logging into the server will be okay on your traffic, but how can you do this on the mac? Thanks ...

Is it possible to update an "order" column from within a trigger in MySQL?

We have a table in our system that would benefit from a numeric column so we can easily grab the 1st, 2nd, 3rd records for a job. We could, of course, update this column from the application itself, but I was hoping to do it in the database. The final method must handle cases where users insert data that belongs in the "middle" of the r...