MS SQL problem

**aaronskw** · Jan 23rd, 2004, 06:02 AM

I'm using odbc to connect to an Access dbase and executing the following php command:

PHP Code:


$ctrquery = "SELECT * FROM Contractors ";

this works fine and records get selected but when i change it to use the replace function:

PHP Code:


$ctrquery = "SELECT *, Replace(tel_no, ' ', '') FROM Contractors ";

no records get selected. Has anyone had the same prob or knows why this is happening?

The replace function works in MS Access!!!!

Any ideas????

**ober0330** · Jan 23rd, 2004, 09:18 AM

Look up the function: substr_replace

actually... preg_replace (click) may be a better fit for you as substr_replace would require you to know where the spaces are... which could be done, but would take a lot more coding.

**aaronskw** · Jan 23rd, 2004, 12:14 PM

Thanks ober,

I was looking to do the replace within the sql statement itself, the function definately works in Access. It doesn't work when I execute the statement in php using odbc_do etc.

**ober0330** · Jan 23rd, 2004, 12:25 PM

so format the telephone number in PHP before stuffing it into the query string. And you could even do the formatting when creating the string... like:

PHP Code:


$ctrquery = "SELECT *, " . preg_replace(whatever, whatever, whatever) . " FROM Contractors ";

Then again, I've never used ODBC to connect to a DB through PHP, so I'm not sure if it would work like that or not. I imagine it would tho.

**aaronskw** · Jan 26th, 2004, 06:51 AM

Nope that didn't work either, I think the formatting needs to be done as part of the sql statement and using an sql function???? I'm executing the odbc stuff as follows:-

PHP Code:


$ctrquery = "SELECT *, Replace(tel_no, ' ', '') FROM Contractors ";

$res = odbc_do ($con, $ctrquery);

while (odbc_fetch_into($res, $row) != False)

{

     // do stuff

}

What I'm tring to achieve is for the user to put in a telephone num into an input box click on a submit button and then remove spaces in the input string which I have already done and then do sql selection and display the results in the while loop etc.

I know I haven't got a where clause above as I am just testing at the moment. Even selecting all recs as above, the selection comes up blank??????

Hope this explains a little better.

**ober0330** · Jan 26th, 2004, 07:55 AM

That explains it fine, but all you're doing is creating a string that is your SQL query. You should be able to use whatever replacement method you want. There is either something wrong with your calling method or some reason no results are being returned. It is not your replace method.

How are the numbers stored in the DB? Can you put in a specific query and get results back?

**aaronskw** · Jan 27th, 2004, 05:35 AM

There's been a further development, a created a test php page as follows:

PHP Code:


<?php

$con = odbc_connect("nuasmcnet00_fieldsales", "", "", SQL_CUR_USE_ODBC );

if ($con == False)

    die("The applcation has failed to connect to the database.  Please contact your system administrator.");

?>

<html>

<head>

<title>Spec Achieved test</title>

</head>

<body bgcolor="#CEE5F7" alink="#ff0000" link="#0000ff">



<?php

$ctrquery = "select Contractors.account_no , contractors.name , contractors.tel_no , replace(Contractors.tel_no,' ','') AS Expr1 FROM Contractors where contractors.name like '%haden%' ";

$res = odbc_do ($con, $ctrquery);

while (odbc_fetch_into($res, $row) != False)

{

  echo "Acc " . $row[0] . "   " . $row[1] . "      " . $row[2] . "&nbsp;&nbsp;" . $row[3] . "<br>\n";

}





?>



<?php

if (isSet($con))

    odbc_close($con);

?>

</body>

</html>

When I load this page I get following Warning message:

PHP Code:


Warning: SQL error: [Microsoft][ODBC Microsoft Access Driver] Undefined function 'replace' in expression., SQL state 37000 in SQLExecDirect in c:\inetpub\wwwroot\specac\ppc\test.php on line 35



Warning: odbc_fetch_into(): supplied argument is not a valid ODBC result resource in c:\inetpub\wwwroot\specac\ppc\test.php on line 36

along with NO results echoed.

**ober0330** · Jan 27th, 2004, 07:49 AM

I give up.

Thread: MS SQL problem

Thread Tools

Display

MS SQL problem

Posting Permissions