Printable View
I'm using odbc to connect to an Access dbase and executing the following php command:
this works fine and records get selected but when i change it to use the replace function:PHP Code:$ctrquery = "SELECT * FROM Contractors ";
no records get selected. Has anyone had the same prob or knows why this is happening?PHP Code:$ctrquery = "SELECT *, Replace(tel_no, ' ', '') FROM Contractors ";
The replace function works in MS Access!!!!
Any ideas????
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.
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.
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:
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.PHP Code:$ctrquery = "SELECT *, " . preg_replace(whatever, whatever, whatever) . " FROM Contractors ";
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:-
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.PHP Code:$ctrquery = "SELECT *, Replace(tel_no, ' ', '') FROM Contractors ";
$res = odbc_do ($con, $ctrquery);
while (odbc_fetch_into($res, $row) != False)
{
// do stuff
}
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.
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?
There's been a further development, a created a test php page as follows:
When I load this page I get following Warning message: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] . " " . $row[3] . "<br>\n";
}
?>
<?php
if (isSet($con))
odbc_close($con);
?>
</body>
</html>
along with NO results echoed.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
I give up. :rolleyes: