[SOLVED] Array help please

[paul2463]

hello Guys

a simple one for all you Gurus out there, but it would mean me writing loads of uneeded code so I thought I would ask for abit of help

 

I have a query(code later) it takes into a formed SELECT query statement, it finds the word SELECT and then finds the word FROM and writes everything in between to a string. I got it to do that no problem, then I created a string with AS statements in it and my whole program fell over, the program needs an array object with just column names in it, my function put in "products.name AS name" not just "products.name". I have tried to sort this out and my code is next but I cannot work out which piece of my new array to put into the final one

<?php
function getBetween($textString)
{
	$mid = "";
	//find the position of the word SELECT
	$pos_s = strpos($textString, "SELECT");

	//find the position of the word FROM
	$pos_e = strpos($textString, "FROM");

	//concatenate a string with everything between them
	for($i=$pos_s+6; (($i<($pos_e))&&$i<strlen($textString));$i++)
	{
		$mid .=$textString[$i];
	}

	//create an array by splitting the new string at the commas
	$commaArr = explode("," , $mid);

	//iterate over the array
	foreach ($commaArr as $var)
	{

	//explode each string by the "space"
	$spaceArr = explode(" ",$var);

	//place the second key value into a new array(usually a space after the comma)
	$columnArr[] = $spaceArr[1];
	}		
	return $columnArr;
}

$query = "SELECT products.idproducts as prod, products.codeProd as Code, products.nameProd as Products,  prodcat.nameCat as Category, supplier.company as Company, products.qtyProd as Quantity, shop.nameShop as Shop  FROM
(((((prodstatus LEFT JOIN products ON products.prodStatus_idprodStatus=prodstatus.idprodStatus) LEFT JOIN prodcat ON prodcat.idprodCat=products.prodCat_idprodCat) LEFT JOIN subprodcat ON 
subprodcat.idsubProdCat=products.subProdCat_idsubProdCat)LEFT JOIN supplier 
ON supplier.idsupplier=products.supplier_idsupplier)LEFT JOIN shop ON shop.idshop=products.shop_idshop)";

print_r(getBetween($query));
?>

// prints out
//Array ( [0] => products.idproducts [1] => products.codeProd [2] => products.nameProd [3] => [4] => supplier.company [5] => products.qtyProd [6] => shop.nameShop )

 

as you can see key[3] is empty because I have put an extra space in the query statement, what I would like is to put the first key value which has text in it, into the final query - hopefully that makes sense

[btherl]

Do you want to keep only those column names in the array which aren't empty?  Everything except the blank one?

[paul2463]

not really,  for instance $spaceArr would more thasn likely be

 

Array ( [0] => [1] => [2] => prodcat.nameCat [3] => as [4] => Category ) 

 

what I would like to be placed into the $columnArr is the FIRST slot with text in it ([1] and [2] have text in but that is a space character which i dont want),  I want prodcat.namecat. This will be different for each query though.

 

Because I cannot account for how many "spaces" there will be, ie in the example there were two spaces after the comma, if there had been 3 then key [2] would also have been"empty" I need to be able to call the , in this case, slot[2]

 

 

[Barand]

try

<?php
$query = "SELECT products.idproducts as prod, products.codeProd as Code, products.nameProd as Products,  prodcat.nameCat as Category, supplier.company as Company, products.qtyProd as Quantity, shop.nameShop as Shop  FROM
(((((prodstatus LEFT JOIN products ON products.prodStatus_idprodStatus=prodstatus.idprodStatus) LEFT JOIN prodcat ON prodcat.idprodCat=products.prodCat_idprodCat) LEFT JOIN subprodcat ON 
subprodcat.idsubProdCat=products.subProdCat_idsubProdCat)LEFT JOIN supplier 
ON supplier.idsupplier=products.supplier_idsupplier)LEFT JOIN shop ON shop.idshop=products.shop_idshop)";

        //find the position of the word SELECT
	$pos_s = strpos($textString, "SELECT") + 6;

	//find the position of the word FROM
	$pos_e = strpos($textString, "FROM");
        
        $select = substr ($query, $pos_s , $pos_e - $pos_s);
        
        $fields = explode (',', $select);
        
        $noAlias = array();
        
        foreach ($fields as $f) {
            list($noAlias[], $alias) = explode ('as', $f);
        }

        echo '<pre>', print_r($noAlias, true), '</pre>';
?>

[paul2463]

excellent Barand, many thanks you have made it even smaller than my original one

 

I will have to look into the list() function and how to use it, never used it before

 

if anyone needs this again here is the function

<?php
function getBetween($textString)
{	
$pos_s = strpos($textString, "SELECT") + 6;
$pos_e = strpos($textString, "FROM");	    
$select = substr($textString, $pos_s , $pos_e-$pos_s);        
$fields = explode (',', $select);        
$noAlias = array();        
foreach ($fields as $f) 
    {
    list($noAlias[], $alias) = explode ('as', $f);
    }       
return $noAlias;
}
?>