Posts by smantscheff

SELECT Course.courseID,courseName,courseInstructor,if(103 in (select studentID from studentLink s where studentID=103 and s.courseID=Course.courseID),103,NULL) as ID from Course;

There must be a better solution, but I'm too tired. Have a look at the EXISTS clause in select statements.

You can group the whole query by the team.id and use the count function on this aggregate.
For further help submit the explicit data structures and your query.

@debasisdas: You are not answering the question.
@gulbano: Yes there is - if you have database or web server logs which recorded the data entry time.

The mysql engine treats both queries as identical. Performance differences may be due to some additional parser activity, but these should not cost more than a few CPU cycles.

I don't know a way to find out past insertion times from the database. If you keep detailed database or web server logs you might be able to identify log entries with database records.

MySQL has a date type called timestamp which records the time of the last update of a row. Add such a field to your table. This field changes with every update, though. If you want to keep the insertion time, use a datetime field and a trigger which updates this field with the value now() after insertion.

Use the one which you understand better. From the MySQL point of view it makes no difference.

Then you have at least one parameter wrong in your mysql call. Try mysql -uusername -ppassword database from the command line first. When that works, add the redirections.

@ardav: One more reason to use the database instead of the PHP function. The MySQL manual states that substr() is multi-byte safe.

Depending on the situation it might be more efficient to use the substring() function in the database query:

$result = mysql_query( 'select substr(myfield,1,10) from mytable' );

You have to define the referenced (foreign) tables first before you can define a table which contains a foreign key. So create the rental tables as the last one.

go along those lines:

$q = mysql_query( 'select * from stock' );
while ($object = mysql_fetch_object($q)) {
  if (mycheck($object) < 3)
    echo "sell at once $object->ticker!";
}

CREATE PROCEDURE ClaimTotal
@Result varchar(50) OUTPUT 
as
select sum(total) into Result 
from claim

Yes, you can have up to 255 of those position values in the definition of an enum field.

Your example is too scarce to give a general solution.
Do all your entires look like "xxx_yyy"? Do you want to search only the part before the underscore? Then use the mysql substr() and locate() function to isolate that part and apply the like operator on it.

How do you keep track of your members? Via mysql or another database? Or with plain text files? Or are they system users?

Use standard input/output direction from the console window. If your script resides in myscript.sql and your output should go in output.txt, enter mysql -u[I]username[/I] -p[I]password[/I] [I]database[/I] < myscript.sql > output.txt on the command line.

Please mark this thread as solved.

It's quite unclear what you want. Do you want all records with a field_id=1? Then just select them. Or do you want all records in which a text field contains the string representation of the number 1? In this case use a regular expression:

select * from mytable wher field_id rlike '(^|[^0-9])1($|[^0-9])'

Also it looks like a flawed database design to have several id entries in one field.

Looks great. Thanks for the hint.

Do it in an orderly fashion with one table for users, one table for permissions, one table which links both and a group_concat function which helps you with your string search.
If you stick with your solution, just do a preg_match for '/(^|,)mila(,|$)/' in row 2 to check if mila is permitted in group 2.

SELECT id FROM members ORDER BY score desc LIMIT 6, 1

Do not care for space, efficiency and waste. MySQL is quite capable handling all these. Optimize your design in terms of usability and legibility.
From a technical point of view, enums are one byte fields with a reference table (hidden in the table definition). Therefore you can have up to 255 enum values in one field. And it's efficient, too.

Think of your material as objects. A vehicle is an object, a tyre is an object. A position is not, but a property of an object, in this case of the object tyre. So the position property (= column) belongs in the tyres table.
If you want to make sure that only a limited range of positions is used, make the position field type an enum field.
Do not use "position codes". Use legible and intellegible field content. A database is not only a technical vehicle but also a means of communication.

Glad to hear that. Please mark this thread as solved.
Move $prevProperty = $property; into the inner if clause (for systematical reasons only).

Set up one table for vehicles, one for tyres. Add a "position" column to the tyres table. Establish a 1:n relation from vehicles to tyres. Set up a unique index on vehicle_id and position in the tyres table to avoid duplicates.

@debasidas: The question was not how to code a partition but about the behaviour in absence of such explicit coding - which I would also like to know.

Use the builtin mysql function group_concat().

MySQL wildcard comparison needs the "like" operator:

if ($_POST[district_zone] == "0") { $receivezone = "like '%'"; } else {$receivezone = "= " . $_POST[district_zone];} 
$query_rs_district = "SELECT property_districts.district_zone, property_districts.district_name FROM property_districts WHERE property_districts.district_zone '$receivezone' ";

Show your CREATE TABLE statements.
If properties and rooms are in a 1:n relation you should be able to build a query for your objectives with a simple join from properties to rooms:

select property.id, room.id from property, room where property.id=room.id_property

This returns only those properties which have rooms associated with them.

You're barking up the wrong tree. Try to build a query which contains all data to be displayed and then loop through it. Do not use 5 queries - it makes your code illegible.
And regarding your code style: avoid redundancies. If mysql_query() goes with "or die..." whereever it occurs then those two belong in a function:

function myquery($sql) { return mysql_query($sql) or die(mysql_error()); }

<?php
$query= "SELECT * FROM table";
$result=mysql_query($query) or die(mysql_error());
$num_rows = mysql_num_rows($result);
if($num_rows > 0)
{
echo "<table>";
while($row = mysql_fetch_array($result))
{
$num_rows--;
echo "<td>" . $row . ($num_rows > 0 ? "," : "") . "</td>";
}
echo "</table>";
}
?>

Try another interface than phpMyAdmin, e.g. command line mysql. I assume that phpMyAdmin twiddles your query. Look in the server log what the actual query is which the server complains about.

If you have wamp installed you can run php from the command line.
In my system it's c: \xampp\php\php <filename.php> You won't have any server variables or HTTP properties like cookies, though.

If you can run it that way but not under apache, then probably apache is not configured correctly. Check your apache config for a line like AddType application/x-httpd-php .php .php5 .php4 .php3 .phtml .phpt

You have to establish a connection first with mysql_connect().
Try

$rst = mysql_query($sql) or die(mysql_error());

to learn more about the problem.

Yes, you need an index on any referenced field.

Make sure that you have an index for all search criteria. And combined indexes for combined search criteria. Let MySQL EXPLAIN your query to optimize it. Add more RAM to your server. Increase the key buffer and other cache variables in my.ini.

$pdf->Output() writes a PDF file. This cannot contain JavaScript. The browser will not understand your code.
For passing variables between scripts use sessions and the $_SESSION array.

You'll have to subscribe to a professional market data service to get quotes without delays. I've searched extensively and not found a single free source.

Take a beginner's course in PHP/MySQL. There are plenty out there.

Since you happened to ask: SQL is not a natural language, nor is any other programming language. Natural languages have built-in redundancies which help avoid disambiguities. By using your mother tongue for meaningful table and column names instead of english you might help yourself to develop code which is easy to understand for yourself even in some years from now. It helps you to realize which part of your code is data manipulation language and which part leans towards semantics. Think of it in terms of syntax highlighting. You wouldn't want all your C# or PHP code look uniform, would you?

All reserved keywords of mysql are derived from english words. Therefore you are better off not using english words for table and column names to avoid conflicts.
Make sure that all components involved - database, tables, columns, server, clients etc. - use an appropriate character set, presumably utf8. Check the settings of the system variables
character_set_client
character_set_connection
character_set_database
character_set_filesystem
character_set_results
character_set_server
character_set_system

What is your aim? If you want to convert some old flat file to MySQL without disturbing much of your application, just export it to CSV and import it into the new table.
If you want to have a clean relational setup, go by the book: eliminate functional dependencies in the table setup and store each piece of information only once.
To get more help, show the table setup.

Try

$sql = mysql_query("SELECT username FROM users WHERE username = '".$username."'") or die( mysql_error();

clients_ClientID is not a column.

if (mysql_num_rows($sql>0));{

should read

if (mysql_num_rows($sql)>0);{

Without looking to deeply into this, first fix the condition

grade = 'F' or null

It should read

grade = 'F' or grade is null

You can do it all in one query and put the display logic into your program code.
Make sure that the fields on which the tables are linked are indexed. Also make sure that you select only the needed fields.

select t1.a,t1.b, t2.c,t2.d, t3.e, t3.f
from tbl_level1_p t1, tbl_level2_p t2, tbl_level3_p t3 
where t1.something=t2.something and t2.something_else = t3.something_else

How do you match existing client entries with existing folders?
If all folders have the same name, you can do it like that (basically an inverted left join):

insert into folders (foldername, client_id) 
select 'ClientDocs', client_id from clients
where not (client_id in (select client_id from clients,folders where clients.client_id=folders.client_id))

select city, sum(rewards) from user, rewards where city.userid=rewards.userid group by city;

They call it a join. Read about it, you need it everywhere in databases.