Right syntax for aliases and/or perhaps this is really a PHP question

Databases
1

I have spent hours trying to get this to work:

try
	{	
		$sql = 
		"SELECT message_instance.id, `clientid`,clientname, `messageid`, `day`, `time`, `timerepeat`, `timestop`, `startdate`, stopdate, msg FROM message_instance 
		LEFT OUTER JOIN message ON message.id = message_instance.messageid 
		LEFT OUTER JOIN client ON message_instance.clientid = client.id
		WHERE message_instance.id = :id";
		$s = $pdo->prepare($sql);
		$s->bindValue(':id', $_POST['messinstance']);
		$s->execute();
	}
catch (PDOException $e)
	{
	  $output = 'Error displaying clients: ' . $e->getMessage();
	  include $_SERVER['DOCUMENT_ROOT'] ."/vvvxxx/includes/output.html.php";
	  exit();
	}

$row = $s->fetch();
...

$message = $row['msg'];
//////////////////////////////////////
$messageinstanceid = $_POST['messinstance'];
/////////////////////////////////////
include 'messageupdate.html.php';


exit();

I could not get

$messageinstanceid = $row['message_instance.id'];

to work for love nor money but then realized the POST variable was available and so have “sorted” the problem.

Grateful for any pointers as I will be hitting this a lot.

BTW tried using aliases but could not get that to work.

Oh also if you use

 'table.field'

notation and put single quotes around the, they fail. Take the quotes of they work fine. So are the quotes a good idea or not?

TIA for any help (especially the first problem).

2

absolutely not

quotes are never allowed around table or column names, they get turned into text strings

you’re thinking of backticks, and even then they aren’t allowed around qualified tablename.columnname

correct syntax would be tablename.columnname

but best practice is not to use backticks at all

3

Thanks for that.

Wonder if you had any thoughts on the original question - the problem : using a table.column in an associative array.

I tried putting it into $stringvariable but that did not work.

I can obviously give all columns unique names throughout the DB but I am sure I am missing something.

Sorry for all these dumb questions I have done no database work for over 35 years - how depressing.

Once I get everything working I will rip it apart and do a tidy version so any “best practice” tips (or I really mean easy to code/easy to maintain tips) would be more than welcome.

4

the only thoughts i had were along the lines of “why do these guys keep posting php questions in the databases forum?”

i don’t do php, i do coldfusion

:slight_smile:

5

Because the PHP code uses a database.
Of course it may also have HTML and/or CSS and/or JavaScript but for whatever reason those forums don’t seem to have the same problems with this as the Database forum does.

Anyway, @shomer; Sorry, but I don’t see in your code where it is “using a table.column in an associative array”, please clarify.
I’m not sure if this is something that could be handled better with a better db design/query or if it is in fact something that needs to be done with PHP

6

LOL OK only asking and it is a mixed PHP/MySQL question and you gave me very helpful MySQL pointers :slight_smile:

Oh and ColdFusion - I never got to grips with it and thought it had almost died (I thought only Adobe backed - anyway sorry that is a bit of an arcane conversation.

The problems was that in the original fetch() I was using (which I think is an associative array)

$messageinstanceid = $row['message_instance.id'];

but it would fail no matter how I tried to escape it. I thought if I put it into a string variable that might work but it did not.

So I have a problem because I have many columns called “id” and need to be able to differentiate them in a fetch like this.

This time I got lucky as the variable in question was the index for the whole select statement so I could go back and grab the $_POST variable but it won’t always be like this.

I can go through all my tables and change all ambiguous columns (might not be bad idea but, particularly on this first build don’t want to do too much revision.

One thing I am pretty sure I can do is split queries into simpler queries but I am sure there must be away around this.

Grateful for any thoughts.

7

give them aliases in the query

SELECT [COLOR="#FF0000"]foo.id AS foo_id[/COLOR]
     , foo.frick
     , [COLOR="#FF0000"][/COLOR][COLOR="#FF0000"]bar.id AS bar_id[/COLOR]
     , bar.frack
  FROM foo
INNER
  JOIN bar ...
8

Dear @983 ; I seriously cannot thank you enough. I had JUST hit this again where I was not going to be able to substitute a $_POST variable in a loop and thought I was stumped and checked on here and that worked PERFECTLY!

There does not appear a Kudos or Thanks button on here. Pity.

Mods a small micro payment system would also be nice. Would love to buy @983 ; a beer or the Bitcoin equivalent.

Thanks a million.

SORTED!! :slight_smile:

9

you just did :slight_smile:

and it’s r937, by the way

10

Oh my goodness sorry. Keep posting when I am exhausted. Sorry for getting your name wrong @r937 ; (BTW is that a clever reference to something I am not getting - like an Eliot 803 or whatever?)

11

[ot]no, it’s just my favourite number

937.com was taken, so i grabbed r937.com[/ot]