Posts by smantscheff

select mem_id, user
from members
where (bypass_purchase = 'P' or bypass_purchase ='Y')
and not (mem_id in select mem_id from position_1)

or

select mem_id, user
from members left join position_1
on members.mem_id=position_1.mem_id
where (bypass_purchase = 'P' or bypass_purchase ='Y')
and position_1.mem_id is null

That is a mistake I won't make again because I'll write a script to do

Yes you will. And your database will crash someday. And then you'd better had some backup mechanism - and if you had, you might retrieve the lost information from there. So set the backup high on your priority list.

You should at least alter the column names. How do you anyone expect to debug this database with names like "s1" or "regno"?

For normalization purposes the subjects should be in their own table and there should be a relation students_and_subjects.

If regno is the student's id and s1 to s4 the grades in up to 4 subjects then you can query the subject count above a certain GRADE with this query:

select regno, if(s1 >= @grade, 1, 0) + if(s2 >= @grade, 1, 0) + if(s3 >= @grade, 1, 0) + if(s4 >= @grade, 1, 0) as number_of_passed_subjects
from sem1
where regno=@student_id

Most probably your child table contains values which are not in the master table. Check that all asset_type values of the child occur in the master.

I just installed phpMyAdmin to see what your problem might be, and it turns out that you are misunderstanding the format options.
Format SQL is for sql scripts which can be parsed and executed by the database.
What you want is format CSV which shows also the field and line delimiter options for your CSV format.

Obviously phpMyAdmin does not recognize the comma as a field delimiter. Switch it to semicolons.

Where do you "click import"? Which interface do you use? What are the settings, especially regarding character sets?
If you want to control all relevant parameters use the mysql command line utility and the LOAD FILE command.

How do you import data? Which error do you get?

If you use the InnoDB engine with foreign keys, both primary and foreign key tables must have indexes on the common key field. Even with other engines indexes in all tables linked by foreign keys will boost performance.

You can
a) use an interface like Navicat which is capable of global search/replace operations;
b) dump the whole database to a file, replace the string on the file and reload it into the database.
To the best of my knowledge there is no mysql function which does what you wish.

select * from project p, replies r
where p.project_id=r.project_id
and
r.mydate = (select max(mydate) from replies rr where rr.project_id=p.project_id group by project_id)

MySQL auto_increment fields are counters which move upward only. With each new record the value for the next record is increased by 1 by the system. Thus you can be sure that in each table you have unique values in auto_increment fields even if you deleted some records in between.
To restart the numbering at zero you have to delete and re-create the table.

The mysql_query function sets an internal pointer, the cursor, to record 0. Each subsequent call of mysql_fetch_array retrieves the current row and advances the cursor by 1 row. If you call mysql_fetch_array 3 times, the result of the last call will be the 3rd row.

I have no clear idea of what you are trying, but this here is definitively wrong:

$z=mysql_query("UPDATE levels SET bal=bal-'$new[$i]' WHERE itemsize= '$item[$i]'");
$result=mysql_query($z);

The result of the mysql_query function is a ressource identifier which you cannot use as input for the same function.

Your design is definitively flawed as one information - the outcome of a game - is stored in two locations: with each team separately.
I'd propose a structure like:

teams (team_id, team_name)
games (game_id, team_1, team_2, game_result)

With this structure it is much easier to count games, to avoid duplicates or to calculate averages.

First of all, this looks like bad table design. A table with similar columns most probably should be split in at least two tables.
Second, you don't tell where your problem is. To count non-zero column values in a stored procedure or function this function will have to contain an expression like

if(game1 is not null, 1, 0) 
+ if(game2 is not null, 1, 0)
...
+ if(game9 is not null, 1, 0)

Is Customer_Infor a spelling error?

All needed information is in the inventory table. There is no need for monthly tables as you can filter data by their timestamps. To avoid checking of double entries you could define a month's inventory as all entries with a timestamp from the this or following month (depending on the time of inventory taking), grouped by barcode and with only the newest item valid. Like in

select barcode, quantity, tstamp from inventory i1
where year(tstamp)=2010 and month(tstamp)=10
and tstamp = (
  select max(tstamp) from inventory where barcode=i1.barcode and year(tstamp) = 2010 and month(tstamp) = 10
)

as a query for the october 2010 inventory.

This is a new question. Start a new thread with it and close this one.

You can get random strings with md5(rand())

It's a bad idea because you will be duplicating data structures. In development you will have to replicate any database design change in all databases.
It's much easier to develop in only one language and to add translations either by means of additional content fields or by means of one big translation table with all interface strings and their respective translations.

Learn how to use the command line client: http://dev.mysql.com/doc/refman/5.5/en/mysql.html
Enter the "SHOW CREATE TABLE ..." statements in the command client and come back with the results.

Use the mysql command line client as your mysql interface, otherwise you will not learn mysql. Or use HeidiSQL which shows you which SQL commands the interface program sends to the database.
"On delete" and "on update" are explained in the manual: http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html
Leave them at their default values.
The table setup sounds o.k., but if it is correct can only be judged by an explicit view on the output of the commands

SHOW CREATE TABLE conveyors;
SHOW CREATE TABLE assets;

In collaborative debugging it's deemed good practice not only to state that an error occured but to cite the error message as precisely as possible.
Your code in my test installation gives not only one but two errors.
1) You have to set the delimiter to //
2) x = x + 1 is not a valid SQL statement. Probably you mean set x = x + 1 Also it's deemed good practice to stick to the spelling of variable names. Try this one:

delimiter //
create procedure enter_data()
begin
	declare x int default 0;
	repeat
		insert into Person values('AAAA');
		set x=x+1;
		until x > 10000
	end repeat;
end //

Design your database first, then the interface.
If the asset type depends on the conveyor number and there is a limited number of asset types, you have a 1:n relation from asset types to conveyors, which in proper database design is expressed as a table Assets (id, asset_type) and a table Conveyors with a foreign key into the Assets table (id, asset_id, ...).
In your data entry interface you offer all asset types as options, for example in a drop-down box or with radio buttons, and you enter in the Conveyor table the associated asset_id together with the other data for the new row.

At least two blatant mistakes in that code.
1) You confuse the POST variables 'Conveyor Number' and 'id'. You do not have a field with name='id' in your form. Therefore you cannot use the value of $_POST.
2) INSERT into conveyor_number (Date) values(NOW(), CURTIME ()
will generate an error - your field list has only one field, but values list two.
And
3) it is semantically incorrect to first INSERT a record from your post variables and then INSERT a second record to keep track of the time of the first record. The 2nd INSERT statement must either be an update statement, or, still better, set the time record at the same time when you insert values into the table. Also the statement

mysql_query("INSERT into conveyor_number (Date) values(NOW(), CURTIME ()")

is syntactically wrong (missing closing bracket).

In general, I recommend that you first learn mysql using the mysql command line client, and then start coding PHP programs which make use of MySQL.

The errors which you mention are PHP errors, not MySQL errors.
Show the code where they occur.
How do you insert a record? Show the code.
There is a difference between conveyor Number and Conveyor Number . PHP array indexes are case sensitive.

Show the output of

SHOW CREATE TABLE conveyor_number

You are confusing PHP errors with mysql messages.
Your line of code

$query_Recordset1 = "SELECT * FROM conveyor_number WHERE conveyor_number.`Conveyor Number` = '$id'

is just a PHP operation and has only semantical links to MySQL but does not execute any MySQL code. $id may be undefined because $_POST is undefined which means that maybe you got a variable name wrong in your HTML form.

The query does not change, regardless of the index structure of the table. As long as you do not explicitly refer to the index in your query, the index structure does not matter to the query statement. The index helps the database engine to answer queries more efficiently, but this does not affect the wording of your queries.
The index structure does matter, of course, to the performance and query optimisation, but in view of your code I doubt that you will tackle those problems anytime soon.

Which queries do you need to adjust? In which queries do you refer explicitly to the primary key?

What exactly is your problem? Go ahead and code a procedure. Come back if it does not work and show us what you did.

What is the output of

SHOW CREATE TABLE `table 1`;
SHOW CREATE TABLE `table 2`;
SHOW CREATE TABLE `table 3`;

?

Google for "mysql replication" or "mysql master slave". If that functionality is not sufficient then code your own database abstraction layer in PHP so that each query which is not a SELECT statement is mirrored on a separate connection to the 2nd server.

What have you tried so far? Have a look at the to_days() and the round() function which may help to solve your problem.

The mysql storage format is YYYYMMDD. If you want to enter the data in another format you have to convert it in your application to and from the target date format.

select * from users where to_days(exp_date) - to_days(now()) = 7

Your where clause is redundant. Since Status has to be one of "In Progress" or "NTC" it cannot be neither "Activated" nor "Done".

Your solution lists only two of the the three possible status values. If you really don't care about the status you can just eliminate it from your query:

SELECT * FROM installs 
WHERE r1 = 'Standard' 
ORDER BY building, appointment_date ASC

select * from test 
where name = 'Meals' 
or parent = (select id from test where name = 'Meals');

MySQL does not have a URL field type which might handle the HTML formatting. You could write a MySQL function which replaces all URL-like text with HTML code. But it's easier done in PHP.

Do you have a PHP or a mysql error? What is the exact error message?

No, this code is not correct. While 0or seems to be a typo, the insert syntax is wrong. The id parameter is lacking a value.

Your brackets are wrong.
Your sub-select returns the number 32, and the outer select cannot find any row with user_id = 1 and unique_id = 32.
Try:

select * from blocks order by unique_id desc where user_id=1 limit 1;

or

select * from blocks where user_id=1 and unique_id=(SELECT max(unique_id) from blocks where user_id=1);

How a string of images is displayed in the browser depends on their size, the css styles working on them, the width of the surrounding box, (div or table) etc. but has nothing to do with how you retrieve them from the database.
Yes, you are confused. First code a working HTML model for your purpose. Then write the code to generate it from the database.
And better start with valid HTML. Do not omit the image sizes for faster page rendering in the browser, do not omit the ALT tag for code validity.

To move records from database A to database B just use this:

insert into B (column1, column2) select column_1, column_2 from A

That way you don't have to tackle any special characters.
Since you seem to be using PHP you don't have to write that "little function" yourself but you can use mysql_real_escape_string (as I told you already).

$query = "INSERT INTO tbl (Id, courseName) VALUES('$Id','"  . mysql_real_escape-string($Name) . "')";

You have to escape the apostrophe with a backslash.
In PHP there is the function mysql_real_escape_string with adds all the necessary backslashes for feeding text into mysql.
The query must read:

INSERT INTO tbl (Id, courseName) VALUES(123,'Apostroph\'d name');

In PHP you have to make sure that the escape character itself gets escaped:

$query = "INSERT INTO tbl (Id, courseName) VALUES(123,'Apostroph\\'d name')";

But urtrivedi already gave you the solution for that.
What's wrong with

UPDATE listing 
SET email=concat( 
  replace(title,' ', ''),
  substr( email, locate( '@', email ))
);

How do you feed those queries into mysql? The error you've got seems to indicate that you are using the wrong tools.
Also let me remark that the listings table shows a very poor design.

That cannot be that much of a problem. Show some actual testdata.

If you do not want all processing in one query you will have to generate the whole bunch of image tags in your query. It might work like that:

SELECT s.Set_Code, GROUP_CONCAT(concat( '<img src="image.php?Product_Code=', t.Product_Code, '"/>&nbsp;' )) AS 'Image'
FROM Thumbnails t
INNER JOIN Sets s 
ON s.Component_Code = t.Product_Code
WHERE s.Set_Code="SBDCR1-WSET"
GROUP BY s.Set_Code

What for do you need the Mimetype in your query?