mysql

Ensuring app SQL select statement retrieve different rows

I have a MySQL InnoDB table containing "tickets". Tickets are either 'open', 'locked', 'closed'. I have multiple remote client apps that run select queries on this table for 'open' tickets. I want to ensure that the different clients don't retrieve the same rows. My process flow right now is: Issue select for open... Update table se...

Why does MySQL value get deleted at the click of browser's 'back' button?

Hello, Can someone explain to me why this happens? I have a file called profile.php, where I want to echo out a user's photo by reading its file path from MySQL. The code is below: echo "<img src=\"{$row['PortraitPath']}\" />"; Some of the code that represents how a file path for the photo is saved is presented below (uploader.php)...

MySQL JOIN query help: return only one row from the right-most table for every row in the left-most table

I have two tables. I want to join them in a way that only one record in the right table is returned for each record in the left most table. I've included an example below. I'd like to avoid subqueries and temporary tables as the actual data is about 4M rows. I also don't care which record in the rightmost table is matched, as long as one...

Slow query, can I speed it up?

I'm retrieving images stored as blobs in the database using a python script running on the same server. SELECT * FROM imagedb_production.imagedb IMAGE LEFT JOIN dccms_production.tblmedia MEDIA ON IMAGE.name = MEDIA.name LEFT JOIN dccms_production.tblmultimedia CAP ON MEDIA.contentItemID = CAP.contentItemID LIMIT 5000,100...

WAMP Setup - Connection resets when php tries to access mysql

I'm trying to install wordpress on a server running windows 2000 (not server edition), apache 2.2, php 5.2.9, and Mysql 5. When I get to page 2 of the setup, where it tries to connect to MySQL to add the tables, the connection is reset. This isn't an isolated error because the same issue is happening when I try to log in to phpMyAdmin. ...

MySQL uniqueness across multiple fields

I am developing an app that needs to match people together. Each person can only match with one other person.. So, in the table below I am trying to make it so that values from user1 & user2 are unique across both fields: CREATE TABLE `match_table` ( `user1` int(11) NOT NULL, `user2` int(11) NOT NULL, UNIQUE KEY `user2` (`user2`...

Loading vhosts from MySQL in lighttpd

I have a MySQL table with a list of domains added by a PHP script. It is literally just a simple list of domains, and all the domains will point to the exact same location. How can I get lighttpd to load these domains? It would be good if there was some built in functionality to automatically check the table for new additions, or just c...

MySQL - removing data that violates a uniqueness constraint

How can data that violates a uniqueness constraint be removed? I have a table that I would like to add a uniqueness constraint for. Inside the table is data that violates that constraint, and I would like to remove it. I don't care at this point which data I lose, only that the final data is good. My first attempts at this have been t...

How to make a controled "shuffle" order?

I have a set of quiz game questions in a sql database (javascript and sqlite actually). The questions all have a difficulty level from 1 to 5, 5 being hardest. Here is a simplified visualization of the data... +---------+--------------+ | id | difficulty | +---------+--------------+ | 1 | 1 | | 2 ...

PHP foreach fails at a random place, with no error given, and only on certain queries

I an issue that has me completely stumped. I am simply running a simple query with one parameter, putting the results in an array, and then looping through the array to display the information in a table. I will post the code at the end, though it is kind of dense, but mostly I'd liek to know what would cause a php script to die with n...

Super general database structure

Say I have a store that sells products that fall under various categories... and each category has associated properties... like a drill bit might have coating, diameter, helix angle, or whatever. The issue is that I'd like the user to be able to edit these properties. If I wasn't interested in having the user change the properties, and ...

Query using JOIN instead of INTERSECT

Hello, I have a schema like this: // table badge_types id | name +++|++++++++++ 1 | mentor 2 | proctor 3 | doctor // table badges id | badge_type_id | user_id +++|+++++++++++++++|++++++++ 1 | 1 | 5 2 | 1 | 6 3 | 2 | 6 4 | 3 | 6 5 | 2 | 19 6 | 3 | 20 ...

MySQL - Print debugging info from stored procedure

Hi, Is there a way in MySQL to print debugging info - something like ... 'print' in SQLServer DBMS_OUTPUT.PUT_LINE in Oracle Thanks. ...

How do I replace spaces into hyphens in an URL.

I have some data that is inputted into to a database with spaces. e.g. first and last names. I then need to call that data from the database and display it as a link so i have a friendly URL. I am not sure if I should do this with mod-rewrite or php. What is the best solution? A solution like below doesn't seem to work str_replace('- ...

how can i recall data from an SQL table TEXT field into and HTML textarea using PHP? (all part of a simple Article Management System)

I building my own simple article management system, and have run into a bit of trouble upon creating a page to edit the articles. I am able to recall the title and tagline of the articles in the form input using the 'value' attribute. However, I am yet to work out how to do the same thing with the form textarea as it has no 'value' att...

CSV with multiple header rows; import into mysql using php

Hi, I've got a CSV file with two sets of data in it [its pulled of a temperature logger]. I'm wondering if there's a way to import the first two rows as one set of data and the rest as a second set of data. I'm familiar with PHP, but have never had to do anything like this. I've attached a subset of data below. Any help or direction woul...

Help forming a complex SQL statement

Hello friends! I'm coming up stumped on forming a working SQL statement for a project of mine. First of all, I have a "questions" table and a "response" table. The response table is tied to the question table through a foreign id called "question_id" (tied to the questions "id"). A question doesn't necessarily have to have a response, b...

matrix/crosstab/pivot query in mySQL

I have a Bill table like this BillNo - 1, ServiceCode -1, ServiceName - 'Lab test' , ItemAmount -30 BillNo -1, ServiceCode -2, ServiceName -'Consultation', ItemAmount -70 I need output like this Bill No -1, Total Bill Amount-100 , Lab Test-30, Consultation-70, Drugs-0, Misc-0 So basically i need the Bill Amount which is the SUM...

Are MySQL datetime and timestamp fields better for PHP apps then Unix timestamp ints?

I was reading over an article that shows some really good information and benchmarks about how well the three different MySQL date/time storage options perform. MySQL DATETIME vs TIMESTAMP vs INT performance and benchmarking with MyISAM While reading the article you start to get the idea that using ints are just a waste and you should...

Trying to write to a MySQL database with a PHP form

I'm trying to do a simple write to database with an HTML form, using PHP. I've run the SQL query in the database and it works perfectly. However, using the form doesn't work. I'm not sure why. Any help? The user/pass/db name are all correct. <?php if(isset($_POST['submit'])) { $con = mysql_connect("localhost","delives0_ideas","ideas")...