Select statement

[mAGOO]

Hi all and thanks in advance.

Is it possible to append a prefix in a Select statement. ie..

$stmt = "select id from tblMembers where ('XXX'.'id'='$the_member')";

Yes I know its messy, but I'm trying to clean things up.

What I'm trying to do is simple in concept, but my limitted skills are giving me a problem.

I have several tables and I'm trying to bring some standard to them thus trying to eliminate some redundant fields. everything is based on an id field which relates to another field call member_id in another table. The only diff between the two is a prefix which is required by some other code. ie. "XXX123" and "123"

What I am wanting to do is to append that prefix in the select statement so I can avoid having to redo tables and code, as this prefix is not required everywhere.

So is it possible to append a prefix in the select statement like I have tried, but failed, to do in the code above.

Cheers
Steph.

[leatherback]

Hi Steph,

Is this something you are setting up just now? then consider NOT having differend ID formats. The id is just that.. An identifier. Editing it, and storing it in different formats in different tables will give you a lot of trouble along the way.

Jelle

[mAGOO]

OK I didn't explain it right.

Lets say I have 2 tables. tblmembers and tblreports

tblemembers has id, memID, name, blah......
id is autonumber
memID = prefix+id

tblreports has id, memID, blah....
id is autonumber
memID is tblmember.id

So what I am trying to get rid of is the tblmember.memID if I can append a prefix to the SELECT statement.

The prefix is actually required for now, so I can't not have it. (This will be another issue later on)

[msk_1980]

If i got it right, you want to make up the table field dynamically in the query.

You should be able to do it by using an variable something like:

$tbl_fld = 'xxx'.'id'; //which would come to xxxid with suffix.

something like:

$tbl_fld = 'cate'.'gory';
$tbl_name = 'mytable';

$query = "select * from ". $tbl_name. " where ". $tbl_fld . "='Album';";

is a valid query.

Hope its the one you are asking for?

[mAGOO]

Well I followed what you did and tried that idea out but it didn't work. I need to be able to return the id from the record with the XXX prefix to equal the id from the other table.

So

tblmember.id = 123

but

tblreport.memID = XXX123

So what I want to be able to do is

tblmember.id to = XXX123

I have tried various syntax to get this but what I usually get is XXX.id = XXX123 and not XXX123=XXX123.

I'm sure it's a simple syntax thing I'm missing but I have looked high and low, never being able to find the answer.

All I'm trying to find out is .... Is it possible to add a prefix in a SELECT statement?

Can I do something like

SELECT * From tblmembers, tblreports WHERE tblmembers(XXX)id=tblreports.memID

[bodzan]

As msk_1980 tried to show you you can append prefix BEFORE andy SQL query no matter to what...

Consider this:

//$id is an id you get from tblmember
$nID = 'XXX'.$id; // $nID should be the id you seek in tblreport table

//so the query you seek should be like:

$stmt = "select id from tblMembers where $nID = '$the_member'";

I figure this is what you are looking for... 🙂

[mAGOO]

OK I see what you're doing and understand it 100%, however, the id is not being returned. All I get is XXXid=XXX123.

here is the entire code for this. It's not mine either I am just trying to make a couple of minor changes.

function InsertReportIntoDB($pirep_array) {
	/* ************************************************************************************************
	   @InsertReportIntoDB
	   Receives a string array with FSAcars pireps and inserts summary into reports table

   Inputs: string array
   Outputs: 1 sucess, 0 error
   ************************************************************************************************ */

/* If this is the first chunk insert PIREP on database */
if ($pirep_array['more']=="0") {
		/* connect to database */
		include(MYSQL_CONNECT_INCLUDE);

		/*
		 * Verify pilot identity (From VA Pilots table) */
		$the_pilot = $pirep_array['pilot'];

		// Section I am trying to edit starts here
		//========================================
		//========================================

		//Suggested code here
// 			$tmp = "select id from pilots";
//    		$pID = $id;
    		$pID = 'id';
			$nID = 'AN'.$pID; 

		$stmt = "select id from pilots where $nID = '$the_pilot'";

		//Original Code here			
// 			$stmt = "select id from pilots where pilot_num='$the_pilot'";
    		$result = mysql_query($stmt);

		//End Section trying to edit
		//========================================
		//========================================




		//Just a quick check for me
		$pID1 = mysql_result($result,0,"id");
		$nID1 = 'AN'.$pID1; 


		/* mysql error */
		if (!$result) {
			$fe = fopen (ERROR_LOG_PATH, "a");
		fwrite($fe, "[ERROR ".date("d.m.y H:i:s")."] ".ERROR_IN_PILOT_QUERY." - Pilot ".$pirep_array['pilot']." - ".mysql_error()." SQL: ".$stmt."\n");
		fclose($fe);

		return 0;
		}

		if (mysql_num_rows($result) == 0) {
   			/* Pilot not found */
   			$fe = fopen (ERROR_LOG_PATH, "a");
		fwrite($fe, "[ERROR ".date("d.m.y H:i:s")."] ".PILOT_NOT_FOUND." - Pilot ".$nID." - 2: ".$nID1."\n");
//			fwrite($fe, "[ERROR ".date("d.m.y H:i:s")."] ".PILOT_NOT_FOUND." - Pilot ".$pirep_array['pilot']."\n");
			fclose($fe);

		return 0;
		} else {
      			/* Pilot found */
      			$id = mysql_result($result,0,"id");

      			/* Insert info on reports table */
      			$values = $id.",'".$pirep_array['date']."','".$pirep_array['time']."','".$pirep_array['callsign']."','".$pirep_array['origin']."','".$pirep_array['dest']."','".$pirep_array['reg']."','".$pirep_array['equipment']."','".$pirep_array['duration']."',".$pirep_array['fuel'].",".$pirep_array['distance'].",'".$pirep_array['rep_url']."'";
      			$stmt = "INSERT INTO reports (id,date,time,callsign,origin_id,destination_id,registration,equipment,duration,fuel,distance,fsacars_rep_url) VALUES ($values)";          			
      			$result = mysql_query($stmt);

      			if (!$result) {
					$fe = fopen (ERROR_LOG_PATH, "a");
				fwrite($fe, "[ERROR ".date("d.m.y H:i:s")."] ".ERROR_INSERTING_PIREP." - Pilot ".$pirep_array['pilot']." - ".mysql_error()." SQL: ".$stmt."\n");
				fclose($fe);

				return 0;
				}

      			/* Close the database connection */
      			mysql_close();
		}
	}

	return 1;
}

function main() {

As you can see I also put a second $nID there called &nID1 to see if the id is returned after the query is called, still same though. I can't, for some reason, pull the id from the table.

I have also tried many variations to try and get this id to come up, some of them have been commented out, but no matter what I have tried, I still can't get it.

Cheers
Steph.

[bodzan]

In this section:

$pID = 'id';
$nID = 'AN'.$pID; 

What is id? Here $nID is going to be ANid and I'm not quite sure that it is what you are aiming at. But, then again, I don't understand it either... 🙂

If I understood it correctly you want to create a row identifier of the table tblmember that is called something like AN123, where 123 would be an id returned from tblmember, right?
If so id in above statements should be returned from the mysql table somehow and I can't see it in that code you're trying to edit. The code shown above canbe created like this and it will do the same thing it does right now:

$nID = 'ANid';

..and I highly doubt that was your idea with it...

Anyways show us what is the form $nID should be because I seem to be lost there...

[mAGOO]

I'm getting confused too. LOL

Going back to the original question, all I want to know is if it is possible to append a prefix to a record in the Select statement. If so how?

This is not a form. What it is, is data being sent from a program to the web. The program records data and then when the task is finished, it sends it to the server.

Here is the table structure:

Table Pilots

id | pilot_num | fname | etc.
1 | AN1 | John | etc

Table Reports

report_id | id | etc
999 | AN1 | etc

So what I am trying to do is to dynamically create the pilot_num since the id and pilot_num are always the same except for the 'AN' in pilot_num.

Thus in the select statement I am trying to append the AN prefix so that pilot.id = reports.id rather than having to use the pilot_num = reports.id