Posts by smantscheff

You confused formatID and formatType. Instead of

$formatID = mysql_query("select formatID from format where formatType = '$format'") OR die(mysql_error());

code

$formatID = mysql_query("select formatID from format where formatID = '$format'") OR die(mysql_error());

Show the table definitions (the CREATE TABLE statements). And your design is flawed. Do not add a column for each category but store categories in their own table and refer to that table.

Google for "mysql java alternative". It all depends on your requirements. With mysql you have a full-fledged SQL DB. Which parts do you not need? Maybe you might even be happy with a simple hash table in text format?

Your clients need access to a mysql server, either on their local machines or a server in their own network or a server which is accessible through the internet. So either you setup a mysql server on each client machine, or you set up a server in their network, or you open your developer machine for mysql queries from the outside. for that purpose you can use dyndns.org or selfip.com if you don't have a fixed IP address.

I don't know about C# but I noticed a design flaw in your query. Do not use more than one literal value to link the two tables:

SELECT * FROM `Families`, `Members` WHERE `Families`.`FamilyId`='132' AND `Members`.`FamilyId`=`Families`.`FamilyId`;

In your sample code you use two different FamilyIds which is exactly the kind of error which might occur with your query.

Please submit some test data (as CREATE TABLE and INSERT statements).
Regarding table design, it would be easier for you with normalized tables. Create a table favorite_game with person_id and game_id instead of your favorite_game_xx fields.

Dear Sir, please be so kind to let us participate in the grandeur of your regal wishes so that we, your lowest servants, might mollify your temper by most humbly submitting a solution to any of your problems, whatever it may be.

Start at the beginning, go right through the middle and up to the end. For further help please clarify your request.

JOIN is a part of a clause for querying related tables, not for design.
You are on the right track. Use foreign keys wherever possible to enhance database integrity. No, they don't harm the performance except with large insert or update operations. But unless you have millions of users you won't notice any difference.
When learning mysql make use of the command line mysql client rather than using phpmyadmin or other higher level interfaces. They do a good job but you don't know what your are doing in terms of data definition and manipulation langugage.

Pritaeas is right about unique fields. You could bracket your select and insert query into a transaction, but that would not exclude the remote possibility that someone with direct access to the database might enter duplicate names and email addresses - for example an admin who is not using your web interface but another database client. Therefore the database design should exclude duplicates where this is needed.

With your design you have to allow NULL values in the foreign key fields so that an order can be related either to a customer or to an admin. But that would leave room for orphaned orders with neither customer nor admin associated with them. Also then an order could be assigned to both a customer and an admin which makes no sense (to me). Therefore I support urtridevis solution: get rid of the admin table. Admins are just users with some special rights and share most properties with customers. They belong in the same table.

Do not feed bookcatalogue.sql to mysqldump but to mysql:

mysql -uroot -p testCopy < bookcatalogue.sql

What for would you need nl2br when storing the values? You need this function to display line breaks in HTML, not to store them in the database. Use

INSERT INTO global_leave_replacement_application (pic_staffcode) VALUES ('$pic_staffcodeS')"

Like I said: use mysqldump and the mysql command line. That way you'll get a clean database dump and all error messages.

How exactly did you export and import it and what error message do you get? Use mysqldump for export and the mysql command line interface for import. Make sure that the mysql user account on the import side has the right to create tables.

If you are working with windows, start xampp-control in the xampp directory for an interface to the various services.
Alternatively you can start/stop the services with
NET [START|STOP] MYSQL
NET [START|STOP] APACHE2.2
from the command line.
Also have a look at the error log in /xampp/apache/logs.
my.ini should be in /xampp/mysql/bin

DESC is a reserved keyword in (my)sql used with ORDER BY. Don't name your fields as reserved keywords.

To my knowledge there is no tee statement in mysql server. You have to process the log file from the OS level or use the mysql command line client which knows how to tee.

Erase the comma before "DESC"

You connect to mysql by supplying a username, a password and optionally a port and a database name to you connecting interface, be it the mysql command line client or JDBC or ODBC or PHP or whatever.
Server versions on linux and windows (WAMP/LAMP/ work alike.
The only practical difference between phpMyAdmin and other clients is that phpMyAdmin may be installed on the same server as the database and therefore can connect to the database locally. Many providers do not allow non-local database access, so phpMyAdmin may be the only one which can be used in such circumstances (or have a look at the Navicat tunneling mechanism).

Have a look at this HTML code. In IE7 it displays two rows of a table-like form, much the same as in Firefox 3.x, 4 and Chrome. Now uncomment the "<!--div>abc</div-->" and look again. Now the row spacing has become much larger, about 1em. I don't have a clue where this comes from and how I could possibly avoid it. I need the <div> above the table to display some text. The effect stays the same if you replace the <div> by a <p>.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en"><head>
<title></title>
<meta http-equiv="Content-type" content="text/html; charset=UTF-8"/>
<style type="text/css">

body {
}
body, table, div {
	font-family: Arial, Helvetica, Swiss, Sans-Serif;
	font-size: 10pt;
	padding: 0;
	margin: 0;
}
div#body {
	position: relative;
	margin: auto;
	height:670px;
	width:1000px;
	margin-bottom: 10px;
	padding-bottom: 10px;

	background-color:white;
	overflow: hidden;

}

div.table {
	margin: 0;
	display: table;
}
div.tr {
	margin: 0;
	display: table-row;
	clear: both;
}
div.td {
	margin: 0;
	display: table-cell;
	float:left;  
}
div.left {
	width:150px;
}
div.right {
	width: 180px;
}

input, select {
	border: 0;
	border-right: 1px solid green; 
	border-bottom: 1px solid green; 
	background-color: #CAEBC9;
}
input.submit {
	border-top: 1px solid green; 
	border-left: 1px solid green; 
	border-right: 1px solid green; 
	border-bottom: 1px solid green; 
	background-color: #CAEBC9;
}
div#content {
	font-size: 10pt;
}
div#contentbody, div#prevnext {
	position: absolute;
	left:180px;
	width: 460px;
	padding-left: 30px;
	padding-right: 20px;
}
div#contentbody {
	top:95px;
	height:500px;
	padding-top: 0px;
	overflow: auto;
}


</style></head>
<body>
	<div id="body" style="">
		<div id="contentbody">
			
			<!--div>
				abc
			</div--> …

Strange indeed. Run a complete log (change the logging configuration in my.ini and restart the server) to see where and why the update occurs. Do you have any active triggers which do anything the the user table?

$check2 is a handle to the mysql query, AKA resource. You have to retrieve the rows from this resource with mysql_fetch_array or mysql_fetch_object or similar functions before you can use the column values.

It's a question of security.
If all domains share the same mysql user you have to define this user's access right's to the database only once. This makes it more manageable that a bunch of users, especially since mysql does not have a concept of user groups.
If all domains have different mysql users it's easier to revoke a compromised access. But since all share the same database and presumably the same web application and even the same data it might be easier for an intruder to hijack another login once he has broken into the database.

I did not study the code thoroughly, but noticed this assignment

if ($check2 = $thenumbar)

which should probably be a comparison instead

if ($check2 == $thenumbar)

The tees I know all have an -a option for appending and overwrite otherwise. Enter tee --help to get an option list.

Use tee without the append option.

Just understand this code that how it works and your problem will be solved-

Of course you will have to check first if the table already exists, maybe include a DROP TABLE statement, sanitize the input so that no malicious user can compromise or erase your whole database, and set the character sets and collating sequences according to your needs.

No, by script I mean a text file containing SQL statements and which can be fed into mysql via the command line by means of input/output redirection (pipes).
Of course you can use PHP as a script language from the command line, too. But do not use it via a webserver with lengthy operations as this will make the workflow quite awkward.

Use two queries and bracket them into a transaction. That makes them from a logical and database point of view only one query in two chunks.
If you don't like that, use this inefficient solution:

UPDATE runquery.table1, runquery.table2 SET 
runquery.table1.`Column1` = if(itemid=3,(runquery.table2.`Col1`/runquery.table2.`Col2`*100),Column1),
runquery.table1.`Column2` = if(itemid=5,(runquery.table2.`Col1`/runquery.table2.`Col2`*100),Column2)
where runquery.table1.`Userids` = runquery.table2.`userid`;

Yes, that's the essence. You can do quite a lot with plain SQL.
As for the workflow: Write a script which start always on a fresh copy of your original database and do not do any manual database editing. Integrate all editing (if necessary) into your script. That way you will be able to apply global changes and correct conversion errors rapidly.

Write a script which imports all CSV data into a separate database. Use the mysqlimport command line utility for that purpose.
Then write an SQL script which compares the imported CSV data with the data in place and updates them as necessary. Store this script as a stored procedure so that anyone can run it from a database interface.
Do not use PHP nor phpMyAdmin as you will spent much time coding for results which are already integrated in the various mysql command line tools. Rather spend your time learning how to use those.

To prevent illicit downloads you have to protect your files on the server level. Otherwise any wisecrack who can guess how a link looks like will be able to download. Tur2tlive's solution is dangerous because it makes you believe that your data are safe while they are definitely not.
To stop foreigners from downloading you have to include a

Order Deny,Allow 
Deny from All

into your server's configuration (either the apache .conf files or the local .htaccess). Look up the documentation for those apache statement. Then you will have to write your own download script which first checks if a user has the required permissions and then serves the file in chunks (don't forget to send the mime headers for the file type). Use PHP sessions for the user's login status.

You can't do that in PHP.
Log the submissions to a text file and view it with 'tail -f theLogFile.txt' from a console window (assuming you are using linux).

What syntax error do you get? I assune it's because the column names in your query are bracketed in apostrophes, which the should not be. Try instead:

mysql_query ("INSERT INTO members(ID,Name,Surname,Phone,Email,Cemail,Username,Password,Cpassword)
	VALUES (NULL, '$Name','$Surname','$PhoneNo','$Email','$Cemail','$Username','$Password','$Cpassword'") or die (mysql_error());

How do you decide if a clause is needed in a query or not? If you have a rule for that, you can code it. But from your example this rule would have to rely on semantic knowledge rather on formal aspects of the query.

Bracket the whole order process with START TRANSACTION and COMMIT respectively ROLLBACK in case of any errors.

Maybe there is a mysql process still blocking the 3306 port. Restart the machine or kill this process (if you find it).
I don't know how close MacOS sticks to its ancestor linux. With linux the advice would be, look under /var/log/messages to find info as to why mysqld would not start.

Did you have any difficulties with your method? If it's not broken, don't fix it.
What is "this variable"? What are you doing now in your script? Are you working with sessions or with redirects or with cookies?

Your problem is bad database design. By which value would you like the aggregate row with idNo 12 to be sorted - 12, 13, or 14/02/2011?
You cannot have distinct rows as a GROUP BY result and at the same time retrieve the non-grouped values separately. The non-grouped values disappear in any query result with aggregate functions. MySQL does a bad job here by displaying randomly just the first non-aggregate field content it encounters.

If you do not specify an AS clause in your field content description, the row index will be the same as the content description itself.
But your query does not make sense in the first place. Test it against your database without PHP (from the command line) and you will see that you get nonsensical results.
You are selecting only one column per row SUM(Reg_no)+SUM(class) , but in your printout you refer to two columns ( Reg_no and sum(class) ) which are both not part of your select query.

create view xyz as 
select *, salary * 10 / 100 as HRA, salary * 9/100 as DA, salary * 19/100 as net from employees

Use the mysqldump command line tool.
You need something like mysqldump -u[I]user[/I] -p[I]password[/I] [I]database[/I] --tab [I]filename[/I] See mysqldump --help for a complete option list.

how to do this

Don't. Do not store calculated values in your database. If Hra and DA are percentages of another database field, create a view which calculates them instead of storing them.

Use a javascript OnKeyUp() function. You cannot do it in PHP without a submit.

Make sure that your query is using indexes. What does "EXPLAIN <your-update-query>" tell you?

1. What does "The last query doesn't work" mean? Do you get an error message or what?
2. Isolate your problem and present exactly the query which does not work and tell us in which way it doesn't work.
3. The clause "WHERE position <= $position AND position <= $position" is tautologic.
4. You use of "row" and "column" is confusing. Use the standard connotation.
5. Learn about the extract() function and apply it to your $_POST array.

Use the "text" data type to store long texts.
If you are stuck with the varchar type you can either store longer texts in external files and store the file names in the database, or you can set up a system of linked varchar entries which you concatenate to get a whole text entry. But both solutions are, from a database point of view, unnecessary and inefficient.

Quote from http://stackoverflow.com/questions/658937/cache-re-use-a-subquery-in-mysql:

See what EXPLAIN EXTENDED says.
If it says DEPENDENT SUBQUERY or UNCACHEABLE SUBQUERY, then it will be reevaluated each time it's used.
This happens if the subquery uses session variables or is a correlated subquery.
If it doesn't, it most probably will be cached.

You have to do it in several steps:
1) Check if there is a link with position 2 in the database.
2) If it is, UPDATE pages SET position=position+1 WHERE position >= 2 3) Now do your INSERT query