Posts by smantscheff

Your 4 (count them: four) user inputs can be tested for duplicate combinations just by one query:

select * from mytable where 
   name = <name>
or surname = <surname>
or DoB = <dob>
or Nationality = <nationality>

If you want to test for combinations with at least two factors, use

select * from mytable where 
   (name = <name> and surname = <surname>)
or (name = <name> and DoB = <dob>)
or (name = <name> and Nationality = <nationality>)
or (surname = <surname and DoB = <dob>)
...

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.

1) Define order_no as an auto_increment field.
2) Enter ALTER TABLE mytable AUTO_INCREMENT = 70000; at the mysql command prompt.

Make sure that all your databases, tables and connections use utf-8 as a character set.
Enter show variables like "character_set%" at the mysql command prompt to make sure that you did it right. Make sure that also the HTML output contains UTF-8 and that the Content-type header looks like Content-type: text/html; charset=utf8;

It depends on your data definition.
If you defined the zip codes as integers you cannot have leading zeros. You will have to add them in processing the data.
If you defined the zip codes as character fields you can use the implicit conversion or the convert function:

update mytable set zipcode = concat("0",zipcode) where zipcode < 10000;

As long as you do not post a concrete question pertaining to mysql you probably won't get an answer.
If you rather do your processing in PHP or in the database is a matter of taste, speed and efficiency. I recommend that all processing which can be moved to the database should go there because then it does not matter if you use PHP or any other interface for data manipulation.
Furthermore your problem seems to be solvable by a (maybe complicated) query. But that is impossible to say without data structures, test data and desired results.

This is how your HTML output for one row reads:

<tr>
<td><font face="Arial, Helvetica, sans-serif">East High</font></td>
<td><font face="Arial, Helvetica, sans-serif">B102</font></td>
<td><font face="Arial, Helvetica, sans-serif">150kg</font></td>
<td><font face="Arial, Helvetica, sans-serif">50g</font></td>
<td><font face="Arial, Helvetica, sans-serif">http://www.weighingscales.com/results2.asp?ID=353</font></td>
<td><font face="Arial, Helvetica, sans-serif">Parcel</font></td>
<td><font face="Arial, Helvetica, sans-serif">£84</font></td>
<td><font face="Arial, Helvetica, sans-serif">372x372</font></td>
</tr>

This is how it should read:

<tr>
<td><font face="Arial, Helvetica, sans-serif">East High</font></td>
<td><font face="Arial, Helvetica, sans-serif">B102</font></td>
<td><font face="Arial, Helvetica, sans-serif">150kg</font></td>
<td><font face="Arial, Helvetica, sans-serif">50g</font></td>
<td><font face="Arial, Helvetica, sans-serif"><a href='http://www.weighingscales.com/results2.asp?ID=353'>http://www.weighingscales.com/results2.asp?ID=353</a></font></td>
<td><font face="Arial, Helvetica, sans-serif">Parcel</font></td>
<td><font face="Arial, Helvetica, sans-serif">£84</font></td>
<td><font face="Arial, Helvetica, sans-serif">372x372</font></td>
</tr>

Advice:
1) Learn some more HTML.
2) Learn how to use CSS to format recurrent elements (like your table rows and cells).
3) Do not store anything in your database which is a constant or can be calculcated from the content. In this case the string "http://www.weighingscales.com/results2.asp?ID=" is a constant which belongs in your code rather than your database.
4) Check your spelling ("incriments").
5) Do not store numerical values as strings (your capacity and increment fields). How would you search for a capacity between 50kg and 100kg in your design?

Your's seems to be an HTML problem, not MySQL. To display a link you have to code it as a <a ...> tag. In your case that might read:

echo '<br/> Weblink: <a href="' . $row['Weblink'] . '">' . $row['Weblink'] . '</a>';

Your problem is not clear. What is the value of the Weblink field and what would you like to display in the browser?

Any function can result in an error. Your code has to deal with that possibility.
Without knowing your algorithm or seeing some pseudo-code of it I cannot help you any further.

Either you send a notification email or you don't. If you do depends on the logic of your program. This has nothing to do with "safety" - if it has, your algorithm is flawed.

1) Start the transaction.
2) Do your processing and send a flag if a notification must be sent.
3a) Commit the transaction OR
3b) Unset the notification flag and roll back the transaction.
4) Check if the notification flag is set and sent the email accordingly.

Show the procedure head and how you call the procedure.

That is what transactions are for. Bracket your check and insert procedure in a transaction which you can roll back in case of duplicate values.

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

Some simple arithmetic shows: if all human beings start adding contacts with the steady rate of one contact per second, night and day throughout, even on holidays, than you will be out of unique IDs after 84 years. You really like to plan it big, do you?

If you use a DBMS like MySQL or the like you can also use 64-bit integers as primary keys. This gives you 18.446.744.073.709.551.615 possible contacts - is that sufficient? If not and you are planning on a galactical scale use a char field with 50 (or so) characters and increment the values by your own routine instead of an auto-increment value.

If you use 32-bit integers for the contact ID field you may store up to 2^32-1 different contacts. That should be sufficient for all practical purposes.

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.

When I test your code, the line

insert into hh values(27.22);

results in the mysql error 1264: Out of range error for column id You have to define the double field with as much decimal places as needed. Change the table definition to

create table hh (id double(5,2));

and it will work.

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?

post_id_to_delete is the id of the post which is to be deleted. Wherefrom you get it depends on your application logic. So how does you application define which posts shall be deleteted?

This is nonsense, too.
Your query

$sql = "DELETE FROM posts WHERE post_id =" . $_SESSION['user_id'];

deletes every post where the post_id equals the user_id. Since both are auto_increment values which do not have any logical relationship, your query will in effect delete arbitrary records with no relationship whatsoever.
You have to save the post_id of the post which you/the user wants to delete in a variable, e.g. a POST or session variable, and then delete exactly this post, like in

$sql = "DELETE FROM posts WHERE post_id =" . $_POST['post_id_to_delete'];

(With this construction, beware of mysql injection, though).

This looks like nonsense.
When you want to delete a record from the post table you have to know it's post_id. You do not need an arbitrary generated value in the user table for that purpose.

Make sure that the connection parameters in my.ini, especially the socket setup, have the correct values.
Have a look at the user contributed notes in http://php.net/manual/de/book.mysqlnd.php

Looks like a 1:n relation from Broker to Agent, which means that the Agent table needs a foreign key (broker_id) into the Broker table.
Further you need a 1:n relation from Agents to Leads. And maybe one from Brokers to Leads (depends on the semantics of your app).

Your PHP is compiled without mysql support.
Create a php file with this content:

<?php phpinfo();

and navigate your browser to that file. It will tell you which PHP modules are installed and active.

Seems quite o.k. You should not store unencrypted plain text passwords for security reasons. And instead of "SELECT *" better "SELECT username,password" for performance reasons.

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

You could use a unique index on the unique field combination an then use "INSERT IGNORE" without a prior check for doublettes.

You seem to be using mysql as a database, not mysqlbuddy. mysqlbuddy is just an interface to the database, as is phpmyadmin, too. It does not matter to the website application which of those you use. I recommend HeidiSQL.
Are you sure that you want to store plaintext passwords? That violates standard security practice.

along those lines

$query = mysql( $sql );
while ($array = mysql_fetch_array($query)) 
  echo $array['user_name'] . '<br/>';

What is the output of

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

?

This structure of games depending on the outcome of previous games is effectively a recursive hierarchy or tree structure. This cannot be properly displayed in plain queries or views. You will need a function which computes for a given match recursively the outcomes of all previous matches to determine the teams for this match.

It seems that any stage belongs to a place, but not any place has more than one stages. Therefore I would set up a database in which
- each concert takes place at a time and a place;
- each place may have several stages;
- each stage belongs to a festival and to a place;
- each concert may belong to a festival.

So we have the entities CONCERTS, PLACES, STAGES, FESTIVALS
A SOLO entity or property is not needed as this property can be computed: a CONCERT which does not belong to a FESTIVAL is a solo.
Hope that helps.

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.

Show the complete script on which you are testing and the error messages of MySQL.
'xyz' ist just the name of a foreign key constraint. It does not matter how it is named as long as the name is unique.

The following script does not generate any errors on my system.

use test;

drop table if exists test_records;
drop table if exists student_detail;
drop table if exists test_metadata;
drop table if exists test_user;

CREATE TABLE `test_user` (
`u_id` int(11) NOT NULL AUTO_INCREMENT,
`u_name` varchar(45) NOT NULL,
`u_password` varchar(45) NOT NULL,
`u_level` varchar(45) NOT NULL,
PRIMARY KEY (`u_id`),
UNIQUE KEY `u_name_UNIQUE` (`u_name`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;
CREATE TABLE `test_metadata` (
`test_id` int(11) NOT NULL,
`test_name` varchar(45) NOT NULL,
`test_ques_num` int(11) NOT NULL,
`test_time` int(11) NOT NULL,
`examiner` varchar(45) NOT NULL,
PRIMARY KEY (`test_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ;
CREATE TABLE `student_detail` (
`Student_id` varchar(45) NOT NULL,
`Student_Name` varchar(45) NOT NULL,
`Class` varchar(45) NOT NULL,
`Semester` varchar(45) NOT NULL,
`u_id` int(11) NOT NULL,
PRIMARY KEY (`Student_id`),
KEY `u_id` (`u_id`),
CONSTRAINT `u_id` FOREIGN KEY (`u_id`) REFERENCES `test_user` (`u_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ;
CREATE TABLE `test_records` (
`test_record_id` int(11) NOT NULL AUTO_INCREMENT,
`test_name` varchar(45) NOT NULL,
`result` float DEFAULT NULL,
`status` varchar(45) DEFAULT NULL,
`Student_id` varchar(45) NOT NULL,
`u_id` int(11) NOT NULL,
`test_id` int(11) NOT NULL,
PRIMARY KEY (`test_record_id`),
KEY `Student_id` (`Student_id`),
CONSTRAINT `xyz` FOREIGN KEY (`Student_id`) REFERENCES `student_detail`
(`Student_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ;

ALTER TABLE `test_records` 
  ADD CONSTRAINT `Student_id`
  FOREIGN KEY (`Student_id` )
  REFERENCES `student_detail` (`Student_id` )
  ON DELETE CASCADE
  ON UPDATE CASCADE, 
  ADD CONSTRAINT `test_id`
  FOREIGN KEY (`test_id` )
  REFERENCES `test_metadata` (`test_id` )
  ON DELETE CASCADE
  ON UPDATE CASCADE
;

Submit a complete test case - CREATE TABLE statements, INSERT statements and the query which does not work - and we will be able to track down the mistake.

You are trying to insert a child record for which no entry exists in the parent table.