(SQL) Help with updating a table

[mark1million]

HI,

I have been messing about for a few days not but cant seem to get the right code.

I want to update a the pireps table flighttype field where flightnum = T1%% that's not important but i cant get it to run.

Basically i have tours that i have now created a separate flighttype for, at the moment they are all in there as P for passenger flights, i want to update them to T for tours as im now reporting in the profiles flight types.

Any help would be appreciated.

Slightly going mad with banging my head against a wall

[Guest lorathon]

try

$sql = 'SELECT *
       FROM '.TABLE_PREFIX.'pireps
       where flightnum = \'T1%\'
       ';

$ret = DB::get_results($sql);

[Guest lorathon]

and then if that works you can loop through

$sql = 'SELECT *
       FROM '.TABLE_PREFIX.'pireps
       where flightnum = \'T1%\'
       ';

$pireps = DB::get_results($sql);


foreach($pireps as $pirep) {

   $fields = array('flighttype' => 'T');

   PIREPData::updatePIREPfields($pirep->pirepid, $fields);
}

[mark1million]

Ah no i have the correct output its just i need to update the database, i ran a query and reset the flighttypes all to P lol. oops

Im in phpmyadmin trying to run the query to manually update the entries.

[Guest lorathon]

Will this work? NOT TESTED. I would copy the pireps table to a backup and play with that one. Don't want to screw up the original.

UPDATE phpvms_pireps
SET `flighttype` = `T`
WHERE `flightnum` = `T1%`

[mark1million]

Thanks Jeff I have already duplicated that table to play with, will give that a go in a bit.

[mark1million]

Same thinig for some reason im getting

#1054 - Unknown column 'T1L%' in 'where clause'

Wall :--( bang lol

The damn thing does exist.

[Guest lorathon]

Try this. Different quotes plus LIKE instead of =

UPDATE phpvms_pireps
SET `flighttype` = 'T'
WHERE `flightnum` LIKE 'T1%'

[mark1million]

Jeff this is driving me crazy lol,

I now have it working with just a minor adjustment

UPDATE phpvms_pireps
SET `flighttype` = 'T'
WHERE `flightnum` LIKE 'T1L%'

Had to replace the `T` with 'T' I hate sql lol

Thank you for your help.

[Guest lorathon]

LOL.. Glad you got it working.

[mark1million]

Thanks again Jeff this was driving me mad.