Mysql in calc help?[Solved]

[m6xmed5]

Hi guys,
I am having trouble with mysql in a calc field.

I want to add a feature to a job tracking system I have that shows an operator what tooling setup they need to used based on the part number from the job they have been handed.

I have 2 tables, one is for tracking the jobs called 'manual_transmission_jobs'
and one for the tooling setup called 'dyno_setups'

The 'dyno_setups' table has a multi-select drop down with a list of part numbers that the row is suitable for (with eval populate from {manual_transmission_parts___part_number} and also a dropdown field called 'which_dyno' (the machine that the tooling list is for).
The 'manual_transmission_jobs' has a dropdown field called part number.

I have created 2 calc fields (because we have 2 machines that have different tooling setups for the same part number).

One machine is called 'axi', the other is called 'fl'

In each calc field I have some mysql code to calculate the rowid for the tooling setup for each machine.
But I can only ever get a result of '1' no matter what the variables are.

Here is my code;

$db = FabrikWorker::getDbo();
$query = $db->getQuery(true);
$dyno = 'axi';



$query->clear()
  ->select('id')
  ->from($db->quoteName('dyno_setups'))
  ->where($db->quote('{manual_transmission_jobs___part_number_raw}'))
  ->like($db->quoteName('dyno_setups.for_testing'))
  ->where($db->quoteName('dyno_setups.which_dyno' . '=' .$dyno));

$db->setQuery($query);
$thepart = $db->loadResult();

var_dump((string)$query);

return $thepart;

Anyone have any idea what I'm doing wrong?

 

[troester]

I don't understand what you are trying to do resp. expecting to get.

What do you get with the var_dump? Is it the query with the values you want to see?
What do you expect to get as result?
loadResult() is always returning one single column (id in your case) of one single record (the first one matching the WHEREs).

 

[m6xmed5]

Just an update,
Seem to be closer with this,

$db = FabrikWorker::getDbo();
$query = $db->getQuery(true);
$partnum = '{manual_transmission_jobs___part_number}';
$dyno = "axi";





$query->clear()
  ->select('id')
  ->from('dyno_setups')
  ->where('which_dyno = ' . $db->quote($dyno) . ' AND ' . $db->quoteName('dyno_setups.for_testing') . ' LIKE  ' . $db->quote($partnum));


$db->setQuery($query);
$thepart = $db->loadResult();

var_dump((string)$query);

return $thepart;

Problem now is that I need to get a % before and after $partnum but I'm not sure how I can do that without it appearing in its own quotes in the query.

This is how it returns now

SELECT id
FROM dyno_setups
WHERE which_dyno = 'axi' AND `dyno_setups`.`for_testing` LIKE '4300002BB0' (length=109)

This is how I need it to return

SELECT id
FROM dyno_setups
WHERE which_dyno = 'axi' AND `dyno_setups`.`for_testing` LIKE '%4300002BB0%' (length=109)

Anyone know how I can do this?

 

[troester]

$partnum='%' . '{manual_transmission_jobs___part_number}' . '%';

 

[m6xmed5]

I don't understand what you are trying to do resp. expecting to get.

What do you get with the var_dump? Is it the query with the values you want to see?
What do you expect to get as result?
loadResult() is always returning one single column (id in your case) of one single record (the first one matching the WHEREs).

Sorry we were writing our replies at the same time, I think my update post changes the questions I had a little. I know loadResult() only returns the first value that matched the query. I'm using a separate calc for each machine to get round this.

 

[m6xmed5]

$partnum='%' . '{manual_transmission_jobs___part_number}' . '%';

Thank you much appreciated.

 

[m6xmed5]

Perfect all working now.
Thanks troester.

 

[troester]

You can get multiple columns and multiple records as objectList or ... or
http://fabrikar.com/forums/index.php?wiki/php-common-tasks/#select
https://docs.joomla.org/Selecting_data_using_JDatabase