Posts by smantscheff

If there is always a second entry with the same 'ver' value as the latest, you might try:

select * from tblp
where (invoice IS NOT NULL)
order by latest desc, ver desc
limit 1,1

It all dependes. Does your table has relations to other tables? Is the primary key used as a foreign key elsewhere? Do you have orphaned records in relations?
If all this is no problem, the easiest way would be this:
Restore the backup table in a backup database (here called backup).
Then

insert ignore into mytable
select * from `backup`.mytable;

That way no new information will be overwritten, and all lost records will be restored.

What kind of a problem is that? Is the number of columns larger than the maximum number of arguments for concat? Or are you just too lazy to type all the names?
No, your suggestion does not work. concat takes string arguments.

You could use the concat function:

select * from mytable where concat(field1,field2,field3...) like '%searchtext%'

This is highly inefficient, but with small databases it works.
An alternative is to export (dump) the database to a text file and then search with grep or a text editor.

select b.brand, c.category
from 
(SELECT DISTINCT brand from product) b,
(SELECT DISTINCT category from product) c

Maybe you could use an arbitrary sorting using the IDs. If the order of posts with the same date does not matter, increase the limit parameter and keep the date until there are no more posts for this date.

SELECT * FROM $logbook WHERE Date>='$Date' AND ID!='$ID' ORDER BY Date, ID LIMIT $i, 1

Increase $i until there are no more posts.

This design is so bad it hurts.
Do not copy records from one table to another to change their status. Just add a status column, as cereal suggested.
Then your code is flawed. You assign some statement text to the $sql variable, but never excecute the INSERT query. Only the DELETE gets executed.
If your departement wants to go productive anytime soon, tell them to install a standard bug tracker like Mantis.

Drop the foreign key relations.
Modify all three tables to the same data type.
Re-create the foreign key relations.

If you want serious help, post a complete test case with table and procedure definitions (CREATE statements), data (INSERT statements), selects and procedure calls, as well as the expected and the actual results.

Look for the value of datadir in /etc/mysql/my.cnf or /etc/my.cnf, respectively. Usually it's /var/lib/mysql.
Unintall mysql with apt-get.
Delete this data directory with all subdirectories.
Delete all in /etc/mysql and /etc/my.ini, /etc/my.cnf etc.
Re-install mysql.

Depending on the size of your database it might be feasible to use concat:

select group_id from search where '%somebody%' like group_concat(value, ' ') and '%special%' like group_concat(value, ' ') group by group_id;

Or make the field an enum type which allows only the predefined values.

1) You cannot mix aggregate functions with non-aggregate fields with valid results. If you group by p.field1, what would you expect in result for the tupels
(field1, field2) = (1,1), (1,2), (2,1),(2,2) ?
Should the query return 1 or 2 as the value for field2?
The database cannot decide that. MySQL has the bad practice of just returning the first encountered value for the non-aggregate field, which is often more than mildly confusing.
2) Your left joins look like regular (inner) joins to me. Or do you really expect NULL values on the right side?
3) To get a minimum or maximum date from a subquery, use the max() or min() function rather than some order. An order clause in a subquery does not make sense except for implementation quirks like then one mentioned above.

Better show some test data (a complete test case with CREATE TABLE, INSERT and SELECT statements) for further help.

You will definitively have less problems if all your stuff - database, scripts, html templates etc. - are in utf-8. If this is a hassle with phpMyAdmin, use a fine database editor like Navicat or HeidiSQL.
If your site has the proper "encoding" attributes in header and meta taga, then the search engines will correctly interpret the typographical quotes as such and not index them.

You can use the primary key in one table as a foreign key in as many tables as you like.
You get constraint errors if your data are not consistent - if you insert a foreign key which does not exist as a primary key or if you delete a primary key which is a foreign key in another table.
For further help show your query and the table structure, and the error message, of course.

Aquilax* solution supposes that the entry date cannot be changed manually after inserting the records. If it can, the query should rather read

SELECT Score FROM scores ORDER BY [B]date[/B] DESC LIMIT 1;

You need root access and administration rights on the server to change that.

There is no right way.
A standard method is a mysql script which you feed first into your development database, then, if all is fine, into your production database, and which contains all necessary statements for table creation and the initial population with test data.
You should not do that with a GUI but rather with the MySQL command line interface - that way you will know what you are doing, and you can integrate it into a batch process.

MySQL does not come with regular expression search/replace functions. Therefore you will either have to write a MySQL function which does the following:
- find a <p> in the text;
- find the matching </p> thereafter;
- replace all line breaks in between by spaces.
Or you write a piece of PHP code (or some other script language) which does the same, but with the help of regular expressions.
Or you export the table in SQL format, load it into an editor like EditPad++ and use the RegEx functions of the editor to replace the line breaks. Afterwards you import it into your table again.

Presumably you use InnoDB tables on your localhost and MyISAM tables on the web server. MyISAM does not support foreign keys with cascading updates and deletes, but it accepts the syntax without error messages.

Put all data of the same structure in one table. As long as you have your data properly indexed the table size does not matter much.
Make sure that all repetitive fields (like product categories etc.) are either enum fields or in lookup tables.
Do *not* use phpMyAdmin for development. Learn how to use the command line interface. Then adopt a decent tool like Navicat or HeidiSQL.

(And of course Debasisas is right. You just don't know enough for such a job.)

Use the function last_insert_id() which returns the last inserted auto-increment number - not per table, but per connection.

Make sure that you have better instruments in place against SQL injection than just hiding column names. Security by obscurity does not work here.

You obviously do not understand the code nor what you are doing.
@ suppresses PHP error messages.
The cited line displays MySQL error messages via PHP. Since no PHP error occurs, nothing get suppressed.
If you don't want to see any errors, delete the OR clause:

$result = mysql_query( $sql );

You won't see any SQL errors then, of course. Therefore in my projects I often add a conditional error display, depending on the login status or the client's IP.

5 minutes ago I did not know that celeroo existed. Then I downloaded it and found

$result = mysql_query( $sql ) or die($sql.": ".mysql_error())

in celerooframe\inc\mysql_wrapper.php This is the line you will have to change to suit your needs.

You will have to modify your framework configuration or code. MySQL does not send any error messages to the web server if not explicitly requested. Look in the framework code for the function calls mysql_errno() or mysql_error() which will most probably lead you to the place which you will have to modify.

Having 7 position tables with the same structure is most likely a bad design flaw.
If your query returns a count of 1 with the GROUP BY clause and a higher count without it, that means that all records share the same pos_id and it doesn't make sense to count the different ones.
Better show some test data. Still better, show a complete test case (with CREATE TABLE, INSERT and SELECT statement.

If a SELECT statement names multiple tables in the FROM clause with the names separated by commas, MySQL performs a full join.

At least on of your weekday tables contains no data, therefore the result join contains no rows.

And the database design is horrendous - having one table for each weekday instead of one table with the weekday as a column is contrary to very basic design principles.

Prepare and show a complete test case, with CREATE TABLE statements and INSERT INTO statements. Then apply your query to this test case and, if it does not work, show it to us.

Show some test data.

This reeks like really bad table design.
You cannot do it dynamically in PHP in one form on one web page because PHP is a server language which does not work in the browser. You have to setup some javascript code in your page which alters the value of the NC/TE number field on change of the dropdown field.
But with that setup you're bound for trouble because you would have to lock the database for updates until the form is sent - otherwise you might end up with identical NC/TE numbers from different clients.
It's better to fill in the NC/TE number on database level in a trigger when the user data are stored and only then to display the auto-generated number to the user.

What for would you like to limit the number of rows in the database?
If you really want, you can check the number of rows in PHP with a construct like

$count = mysql_num_rows( mysql( 'select * from mytable', $connection ));

and disable the insertion of new entries if count exceeds your limit.
If you want to have the limit for display purposes, use the LIMIT clause in mysql instead. Example:

select * from mytable LIMIT 10, 10;

Presumably it's

select child.cat_name, child.cat_desc, parent.cat_name
from mytable child, mytable parent
where child.pk_cat_id = parent.fk_cat_id

A test case is a series of SQL statements which create tables, fill them with data and contain the query which poses a problem for you.
It typically consists of
- one or more CREATE TABLE statements to set up the tables,
- several INSERT INTO statements to fill them with test data, and
- a query (THE query) which you have problems with.

It is not a question of PHP. PHP can display anything which the database delivers.
Your database query is a very basic one. I suggest you take a mysql primer course before you ask more questions which you will know how to answer after the first lesson.

Submit a test case with CREATE TABLE and INSERT statements which shows the problem with your query.

Do not use PHP until you know how to code your MySQL queries.
List all tables which you need in the FROM clause and describe their relation in the WHERE condition - which fields have to match in one record?

select ... (desired fields here)
from tbllabchem, tbldoctor_info, tblpatient_info
where MR_No = RelationMR_No and ID_No = RelationDoc_ID

Use the comnand line utilities mysqldump and mysql to dump your database and feed it into another server.

What does $row_details contain?
If I understand you correctly, you want to make the value set for the field gender (for example) editable. You also want that the field gender has only a limited set of possible values. The correct datatype would be an enum field. You could read the definitions and possible values using the "show create table" statement into your application for form display, and you could expose them for editing in an admin interface which allows for changing the table structure.
An alternative would be a lookup table for the field gender (and all other fields with limited datasets) - this is where you might use the group_concat function.

You are asking for problems. In effect you are going to de-normalize your table, which may ease a short-term purpose, but in in the long run you will stumble into problems because auf bad design.
Then from your example it is not clear which value for "id" should be saved - the first one encountered? Or which?
And how many columns should your new table have? As many as the group with the most rows for the same cid? And the other fields stay empty? Bad, bad design.
Have a look at the group_concat function. Maybe a query like

select cid,group_concat(value) from mytable group by cid

might suit your purpose.

The tables are the output of the mysql command line client.

Consider this:

drop table if exists mytable;
CREATE TABLE mytable (
     id int, name char(20), parentid int, position int, rating int
    );
INSERT INTO mytable VALUES
(1, 'mike', 1, 1, 6),
(2, 'dave', 1, 2, 5),
(3, 'paul', 1, 2, 7),
(4, 'john', 1, 2, 3),
(5, 'mick', 5, 1, 8),
(6, 'owen', 5, 2, 2),
(7, 'rick', 5, 2, 9),
(8, 'jaye', 5, 2, 3);

 select t1.*,t2.*, parents.* from mytable t1
        left join mytable t2
        on t1.parentid = t2.parentid and t1.rating > t2.rating
        join mytable parents
        on parents.id = t1.parentid
        where parents.name rlike 'mike|mick'

+------+------+----------+----------+--------+------+------+----------+----------+--------+------+------+----------+----------+--------+
| id   | name | parentid | position | rating | id   | name | parentid | position | rating | id   | name | parentid | position | rating |
+------+------+----------+----------+--------+------+------+----------+----------+--------+------+------+----------+----------+--------+
|    1 | mike |        1 |        1 |      6 |    2 | dave |        1 |        2 |      5 |    1 | mike |        1 |        1 |      6 |
|    1 | mike |        1 |        1 |      6 |    4 | john |        1 |        2 |      3 |    1 | mike |        1 |        1 |      6 |
|    2 | dave |        1 |        2 |      5 |    4 | john |        1 |        2 |      3 |    1 | mike |        1 |        1 |      6 |
|    3 | paul |        1 |        2 |      7 |    1 | mike |        1 |        1 |      6 |    1 | mike |        1 | …

Have a look at the mysql clustering and partitioning facility. You may design your DB as usual and distribute the load to several servers. I don't know how well this works with really huge DBs, but it might be worth considering.

Thanks for the update. I believe that your database design has some flaws which lead to your complex queries. But, as they say, never change a running system. And never run a changing one.

$query = "SELECT customer_id
FROM discounts
WHERE customer_id='{$customer_id}'";

This is tautological nonsense.

It is not clear how you deal with recurring customers. Do you give a discount for every 8.000 units? Or every time a customer buys something? Or only during a fixed period?

As I said before, if you have a function which gives a unique and reproducible dicount code for every incident and customer, you can set a unique index on the discount field and just insert all records which satisfy your discount conditions without checking for double entries. So your first task should be the design of this discount code generator.

Going around in circles.
In this test setup, you want email1 because it links to bim24. You want email3 because there has no mail yet been sent to email3. Why do you want email2?
Also, the subselect in your query still does not make sense. It leads to a tautology.
The subselect selects all from sent_table where cusid='bim24' . Because this select is right table in the left join, all right table rows have either cusid='bim24' or isnull(cusid) . So the subsequent Where clause WHERE cusid IS NULL OR cusid='BIM24' in effect selects all rows in the right table, and, because of the left join, all in the left table too. So you are just selecting all rows from main_table in a quite complicated way.

But this is exactly what my query where I introduced the ifnull function does: It selects first all records from the_main_table which do not have a matching record in sentdata, and second and additionally all records in sentdata.
Test case:

drop table if exists main_table;
create table main_table (email char(10));
insert into main_table values ('email1'),('email2'),('email3');
drop table if exists sent_table;
create table sent_table (email char(10),cusid char(10),date date );
insert into sent_table values ('email1','bim24','2012-02-01'), ('email2','bim56','2012-02-01');
select mt.email, ifnull(sd.date,'1970-01-01') as theDate, cusid
from main_table mt
left join sent_table sd
on mt.email=sd.email
where cusid is null or cusid='BIM24'
order by theDate;

Result:

+--------+------------+-------+
| email  | theDate    | cusid |
+--------+------------+-------+
| email3 | 1970-01-01 | NULL  |
| email1 | 2012-02-01 | bim24 |
+--------+------------+-------+

I do not understand any more what you're trying to get at. Could you prepare a test case with just 2 or 3 rows which shows which ones you would like to include and which not?
I also do not understand the subselect. Why not like this:

SELECT mt.recid, mt.email, sd.datetime, cusid
FROM the_main_table mt
LEFT JOIN sentdata  sd
ON mt.RECID = sd.RECID 
WHERE `cusid` = 'BIM24'
ORDER BY sd.datetime ASC
LIMIT 5000000;

This runs on my test system in 3 seconds.

Yes you can.
To be more explicit, show us the code for generating the discount code and the condition under which it should execute.
And before you code in PHP use the MySQL command line for testing until you know what you want to code.

Maybe this could be an approach:
Make a left join from the_master_table to sentdata.
For any missing rows on the right insert a date way back in the past and sort by this virtual date as well as by actual dates. That way you get all the records which are missing from the right side as well as the oldest ones from the right side.
Like in:

select mt.recid, mt.email, ifnull(sd.datetime,'1970-01-01') as theDate, cusid
from the_main_table mt
left join sentdata sd
on mt.RECID = sd.RECID 
where cusid is null or cusid='BIM24'
order by theDate 
limit 50000;