vinny42

So we're not looking for sequences after all, any combination of three columns will do.

Database migrations are never as simple as you had hoped. At first it seems a matter of changing a few quotes and some alter-table statements, and just when you think you're ready, one query refuses to work and it will turn out that a major part of your application relies on a databasequirk that the new database doesn't have. Sites developerd on MySQL are usually full of those "mistakes" because MySQL hardly does any sanity checks. So: if you're close to launch, stick with MySQL for the launch and take your time to do a proper migration later. Once you do migrate, I'd suggest that you also look at PostgreSQL, which is opensource, free of licence fees, and *much* more mature than MySQL. It follows the SQL standard much more closely while keeping things like LIMIT. It has good support for XML and the upcoming 9.3 release will add a bunch of JSON features so your webapp can send data to the database directly in the shape of a JSON object, and the database can return information as a JSON object, practically removing the need for PHP. But I'm letting myself get carried away as usual. :-)

Then there are duplicate id's :-)

Just to confirm, this data has seven numbers per row, the queries we've used so far have had 6....

The query should not exclude anything. The only way that a combination might not bne counted correctly is if the numbers are stored as a varchar and the content is not a clean number, but has spaces, newlines etc in it. Then a "1" is different from a "1 " and a " 1". Can you replicate the problem using a small set of testdata that you can post here?

In order to get the count per combination you must add the group-by clause, and you should change the UNION to "UNION ALL" because just "UNION" will actually filter out duplicates. SELECT balls, COUNT(*) as occurence FROM ( SELECT CONCAT_WS(',', Ball1, Ball2, Ball3) as balls, Ball1 as `A`, Ball2 as `B`, Ball3 as `C` FROM random UNION ALL SELECT CONCAT_WS(',', Ball2, Ball3, Ball4) as balls, Ball2 as `A`, Ball3 as `B`, Ball4 as `C` FROM random UNION ALL SELECT CONCAT_WS(',', Ball3, Ball4, Ball5) as balls, Ball3 as `A`, Ball4 as `B`, Ball5 as `C` FROM random UNION ALL SELECT CONCAT_WS(',', Ball4, Ball5, Ball6) as balls, Ball4 as `A`, Ball5 as `B`, Ball6 as `C` FROM random ) AS t GROUP BY balls

You left out the 'x' which was the subquery's alias in the original query.

Ok, so you are looking for how many times any random combination of three numbers occurs in all draws? That would give you results for 111 through 999, correct?

Are you looking for sequences or combinations? If you are looking for 2 4 6, does 1,2,3,4,5,6 also count? Your current model doesn't make this easier, what you'd want is information about the draw, the number and the position of the number in the draw. so, 5,8,2,4,1,9 would be easier to process if it was stored as: draw_id, number, number_position 14, 5, 1 14, 8, 2 14, 2, 3 14, 4, 4 14 ,1, 5 14, 9, 6 because in ordr to know that the numbers are "in a sequence" you need to know their relative positions. I'm trying to think of an intelligent way to solve this problem, I'm getting flashes of "einsteins puzzle" which also has particular sequences and a *huge* number of permutations to check. it is solved by creatively self-joining. I'm not sure if that would work here though...

If timeouts aren't the problem then there are two options left; either the resultset is too big (try doing a count() without selecting any columns, and see how many rows the query expectes to return) or the query is causing the MySQL backend to crash, although I would expect an error like "server has gone away" when that happens. However, this is MySQL, nothing is ever what you expect it to be :-)

Locks should apply until you release them, ie: either you explicitely release the locks, or you end the transaction. The lock *should* last until the end of the procedure. That waid, I' curious about what you are doing here, selecting a count to decide what to do, and locking records in a table that you are not updating sounds like you are trying to do something like: INSERT INTO anothertable SELECT * FROM the_table_you_are_lcoking WHERE condition_for_the_locking_query;

The way MySQLi deals with prepared statements is simply idiotic (I guess that is to be expected when you mix a weird language with a silly database) but did you try the code I used (which I took from the manual) because I can't reproduce the issue with the code I posted and I am curious if you can.

I use the loop to run the search query several times, using a different length searchstring every time. If there is a length that doesn't produce 4 results, this routine will find it. It's a very basic unittest.

Thaat query is weird, you select one Ball1 through 6 from the same record, but as separate rows, that smells like either a bad design, or you are trying to do something that I don't understand. As for your question, I think it would help if you gave an example of what you are looking for.

That code uses pass-by-reference, which has been abolished a *long* time ago, are you running an ancient version of PHP or do you have error-reporting disabled? This works for me: CREATE TABLE `mytable` (`myname` VARCHAR(50) NULL DEFAULT NULL)COLLATE='utf8_general_ci'ENGINE=InnoDB; INSERT INTO `mytable` (`myname`) VALUES ('SomethingInYourDatabase-setupforString'); INSERT INTO `mytable` (`myname`) VALUES ('SomethingInYourDatabase-setupforString2'); INSERT INTO `mytable` (`myname`) VALUES ('3SomethingInYourDatabase-setupforString'); INSERT INTO `mytable` (`myname`) VALUES ('4SomethingInYourDatabase-setupforString5'); <?php echo '<pre>'; $nameSearch = "SomethingInYourDatabase-setupforString"; $mysqli = new mysqli($serverName, $userName, $password, $database) or die('There was a problem connecting to the database'); if ($mysqli->connect_errno) { echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error; } mysqli_query($mysqli, "SET NAMES UTF8"); $sql = "SELECT myname FROM mytable WHERE myname LIKE CONCAT('%',?,'%')"; for ($i = 1; $i < 12; $i++) { $strSearchText = substr($nameSearch, 2, $i); echo 'Searching for "' . $strSearchText . '"<br/>'; $stmt = $mysqli->prepare($sql); if (false === $stmt) { die('Query Prep Failed' . $mysqli->error . $mysqli->errno); } $stmt->bind_param('s', $strSearchText); $stmt->execute(); if (false === $stmt) { die('execute() failed: ' . $stmt->error); } $stmt->store_result(); $rowCount = $stmt->num_rows; if ($rowCount == 4) { echo '<b>OK</b><br/>'; } else { echo '<b>FAIL!!</b><br/>'; $meta = $stmt->result_metadata(); $variables = array(); while ($field = $meta->fetch_field()) { $variables[] = & $data[$field->name]; } call_user_func_array(array($stmt, 'bind_result'), $variables); $intCountResults = 0; while ($stmt->fetch()) { var_dump($variables); $intCountResults++; } } } exit;

Exceedingly odd. Can you reproduce the problem in a small standalone example so I can run it on my system to see what happens?

I had a feeling you were new to the whole SQL thing :-) First a warning; replacing newlines with BR tags in the HTML is usually not a good idea because a newline formats the HTML source, whereas a BR tag formats the rendered HTML page. If the source contains "<br>\n" (this is very common in html) then you definately do not want to replace that with "<br><br>" because your page would look different. What's more, you cannot go back to "<br>\n" because of the same reason. You have been warned. That said; replacing a string "\n" with "<br>" in the column named "your_column_name" in the table named "your_table" is as simple as this: UPDATE your_table SET your_column_name = REPLACE(your_column_name, "\n", "<br>"); See also: dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_replace

Weird. Is your table UTF8 or Latin1? And what is your client-encoding setting?

Don't use INSERT IGNORE because you neverwant to ignore errors or warnings. There are a handfull of applications that do benefit from the IGNORE, but basicallt IGNORE is one of the many ways that MySQL cheats on proper database use by making it possible to say "look database, I don't care that my data doesn't match my schema, just work with it and don't complain". Like mac_gyver says; this is the correct procedure: 1. create a unique constraint (unique index) on whatever it is that makes a project unique. 2. When creating a new project, just run a regular INSERT query. Don't use IGNORE because you don't want corrupted data. 2a. If the INSERT would violate the UNIQUE then MySQL will give an error and PHP will catch that. 2b. If the INSERT worked then last_insert_id() will return the id of the new record. Thus, you only need one query to create a new project.

Have you tried using '%flashlight%' as input, without the CONCAT: $sql = "SELECT * FROM table WHERE name like ?"; Just to see if the problem is in the combinaton of the CONCAT and the prepared statement (which I doubt, but this is bughunting so you neever know...)

I don't understand what you mean. Yo cannot arbitrarily replace newlines with BR tags, that would break all your data. I'd suggest that you should import the CSV without making any replacements using phpmyadmin (which is a *crappy* tool anyway) and use UPDATE queries on the columns where you want to replace newlines with BR tags.

Rebuilding from PHP code is a pretty difficult job. Most MySQL users use wonderful queries like SELECT * everywhere, so MySQL won't tell you which columns are missing, PHP just complains about missing indexes in arrays, somewhere. So no, there is no easy way, it's manual labour looking at queries, looking at which fields in the resultarrays the script uses, hours of fun! This kind of disaster is exactly why I insist on having (apart from backups and backups of backups)) a "structure.sql" file in the sourcecode somewhere, preferably with some test data. PS:mac_gyver is right about refactoring, but do *NOT* do *ANY* of that until you have completely rebuilt the database an verified that it's correct. Making changes now will cause you to loose valuable information about the schema.

Are you sure this works? Youre doing a DISTINCT * which means you are getting duplicate rows through a mistake in the query. de GROUP-BY is further evidence of this bevause you are not doing any aggregation. This is not good. What are you trying to achieve here?

Do you get the HTML into the database and it's only the newlines that are missing? How are you looking at the HTML after importing, are you perhaps looking at it in a browser (because browsers don't render newlines)

What's the exact query that is being executed?