Get result from table and convert this result to another form using php page connected to MySql Database

Question

0.00/5 (No votes)

See more:

i have the table lecture_table shown below
------------------------------------------------------------------------------------
| lecture_name| lecturer_name | class | day| start | end |
|-------------+------------------+-----------------------------+----+------ +------|
| Math I | Ammar Al-Essa | Computer Engineering Class 1| 0 | 8.5 | 10.5 |
------------------------------------------------------------------------------------
i try to

search by lecture startup time and i want to type lecture time such as"08:30 AM" instead of 8.5 then this value converted to 8.5 like in the table above.
the result i want to be shown like "08:30 AM" for start time and "10.30 AM" for end time.
as you can see the day is also an integer number and i already convert the entry from sundry to 0 in database to compare it "Sunday" not as "0".

the final result should be like this
-----------------------------------------------------------------------------------------
| lecture_name| lecturer_name| class | day |start | end |
|-------------+----------------+-----------------------------+-------+------ +----------|
| Math I | Ammar Al-Essa| Computer Engineering Class 1|Sunday |08:30 Am |10:30 AM|
-----------------------------------------------------------------------------------------

one last thing i used this code to covert days but it didn't work when i used it with times

$raw_results = mysql_query("SELECT lecture_name,
lecturer_name,
class,
start,
end,
CASE day
WHEN 0 THEN 'Sunday'
WHEN 1 THEN 'Monday'
WHEN 2 THEN 'Tuesday'
WHEN 3 THEN 'Wednesday'
WHEN 4 THEN 'Thursday'
WHEN 5 THEN 'Friday'
WHEN 6 THEN 'Saturday'
ELSE 'Unknown'
END AS x
FROM lecture_table WHERE (`lecture_name` LIKE '%".$name."%')") or die(mysql_error());

any support will be highly appreciated

Posted 2-Sep-14 0:27am

Hussain Ali

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Manikandan Subbu · Answer 1 · 2014-09-02T01:41:00

First lets see about the start and end field. While creating the table, set the datatype of these two fields as TIME. Now, while you insert into the database insert in the format HH:MM:SS. While you retrieve also, you will get in the same format. If you want to format it in someother way, you can do so using php.

Now coming to the day field, change its datatype to DATE and store the date in the database. After retrieving the date in the php, format it and display the Day like,

PHP

echo date("l",$date);