Merging/Changing previously formatted php code formatted for MySQL to post from API

[SnowInCo]

This is a newbies forum, correct?

I have several pages of php that I created about 4-5 years ago for a client.
The client previously connected to about 5 tables in mysql.
Now I'm tasked with "just changing the mysql connection to the API we created".
So they want to maintain populating the exact same look and feel but posted via an API.
The client created the API I have a nondisclosure to deal with so I can't post it.

After lots of trial and error, I was able to call the data into a webpage with the code below, but now I'm now at a point where I have to really look at all of the fields and formatting that I did several years ago.

1.) Please look at my new code (of which I should source but can't find it). Should I be thinking about it in a different way? Maybe I should call the api data and have it post into a table in mysql (or is that even possible)?

2.) Can anybody provide me with tips or tricks as it relates to keeping the reformatting of my php code to a minimum?

NEW CODE (works right now, but it might be the wrong approach.. also, I didn't want to edit the php.ini file):

<?php

$ch = curl_init();
curl_setopt($ch, CURLOPT_URL, "http://www.clientserver.com/api/nameofapi?primaryfieldname=desiredvalue");
curl_setopt($ch, CURLOPT_HEADER, 0);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, TRUE);
$result = curl_exec($ch);
curl_close($ch);
$result = preg_replace("#(<\s*a\s+[^>]*href\s*=\s*[\"'])(?!http)([^\"'>]+)([\"'>]+)#",'$1http://www.clientserver.com/api/nameofapi?primaryfieldname=desiredvalue/$2$3', $result);
echo $result

?>

ONE OF THE EASIER PAGES OF OLD PHP CODE (didn't include a bunch of html table formatting):

<?php

$username = "uname";
$password = "pwd";
$hostname = "hostname"; 				

$dbhandle = mysql_connect($hostname, $username, $password)
or die("Unable to connect to MySQL");

$selected = mysql_select_db("dbname",$dbhandle)
or die("Could not select dbname");

$query = mysql_query("SELECT* FROM tblA ORDER BY field1");
while ($row = @mysql_fetch_array($query))


{

$variable1=$row["field2"];
$variable2=$row["field 3"];

echo "<tr bgcolor=\"#dddddd\"><td><center>";
echo $row["field1"];
echo ($variable2 != '') ? "<a href=\"$variable2\"></br>variable2fieldname</a>" : '';
echo "</center></td><td><center>";
echo $row["field4"];
echo "</center></td><td><center><span class=\"style2\">";
echo $row["field5"];
echo "</center></td><td><center>";
echo $row["field6"];
echo "</center></td><td><center>";
echo ($variable1 != '') ? "<a href=\"mailto:$variable1\">Email</a>" : '';
echo ltrim($row[""], '0');
}?>

[johanafm]

I'm not sure if I understand what you are trying to achieve

For example…

SnowInCo;11038091 wrote:

Now I'm tasked with "just changing the mysql connection to the API we created".

This indicates that instead of fetching data from the database, your system should fetch it via their API. So instead of connecting and sending queries and updates directly to the database, you would send GET, POST, PUT and DELETE requests to their API.

SnowInCo;11038091 wrote:

So they want to maintain populating the exact same look and feel but posted via an API.

This indicates that you should not change anything regarding presentation of data.

SnowInCo;11038091 wrote:

$result = preg_replace("#(<\sa\s+[^>]href\s=\s[\"'])(?!http)([^{\"'>]+)([\"'>]+)#",'$1http://www.clientserver.com/api/nameofapi?primaryfieldname=desiredvalue/$2$3',} $result);

But then why would you need to change links in those pages? It is your current php scripts that would change from fetching data directly from the database to fetching it via the API that is changing, nothing else.
Moreover, should you really need to change those links, it should be done in a manner which allows for one single replacement. Not doing the same work every request. This could mean that you do the replacement and update the database, for all records. Or that you store only data-parts of the URLs in the database and add domain or whatever when sending the response. Or perhaps you could handle it using url rewrites.

SnowInCo;11038091 wrote:

After lots of trial and error, I was able to call the data into a webpage with the code below, but now I'm now at a point where I have to really look at all of the fields and formatting that I did several years ago.

Is this what you were looking for? Does it need further processing? I can't see it&#8230; I do not know what you want&#8230;
Assuming they provide data in the exact same format as you previously got it from the database, you should just use it just as you used the db result sets and there should be no problems at all.

SnowInCo;11038091 wrote:

1.) Please look at my new code (of which I should source but can't find it)

"of which I should source" == of which I should post the page source ?
You request that script in a browser, then rclick it and select show page source&#8230; How can you not find that?

SnowInCo;11038091 wrote:

Maybe I should call the api data and have it post into a table in mysql (or is that even possible)?

Everything is possible, with this being easily possible.
1. get data
2. save data in database

The questions you need to ask are
1. what results do they want
2. how do you achieve those

If they want you to
- fetch data via API instead of directly from the database
Then it sounds as if you shouldn't be doing anything with any database at all.

[SnowInCo]

johanafm, thank you so much for taking the time.
"Yes!" to most of what you have written.
-My mention of not being able to find the source was related to the fact that I found that php/api online somewhere and it ultimately worked for me. But I couldn't find the page I found it on. I just wanted to credit the writer of that code and not insinuate that I had written it myself.

I know there is no way they are going to give me the data in the format that is needed, but their field names will match the filed names I have in my existing code.
It sounds like my best route is to pull the data and post it into my existing tables within mysql.
I was playing with all sorts of cURL code but I got nowhere and then I found a bunch of information about the php.ini file and how I need to turn "allow_url_fopen = on" which results in a security risk.

This is simply uncharted territory for me. The API developer posted more information as shown below.
But at this point I need to weed through cURL vs XML vs all the other options to figure out the best way to POST to my existing tables.
The good news is there is only one-way communication. It is read only and there will be no need to delete their API data.

Once I get connectivity down, how to do the arrays is a quite intimidating.. I'm seeing quite a bit of fluctuation in various libraries.

Are you able to give me any direction?

GET api/v1/apiname/getfieldbyfieldID?intFieldID={value}

Looks up primary field in a particular location

Request Information
URI Parameters:

Name = intFIeldID
Description =The ID number of the location that the lookup is for
Type=Integer
Additional information=required
Body Parameters=None

Response Information
Resource Description:

APIName

Collection of APIName
Name/ Description /Type /
fielda / Gets or sets field ID number/integer
fieldb/ Gets or sets name/ string
fieldc/ Gets or sets number/ integer
fieldd/ Gets or sets address/string
fielde/ Gets or sets address2/integer

Response Formats

FOR application/json, text/json
Sample:
[
{
"fielda": 1,
"fieldb": "sample string 2",
"fieldc": 3,
"fieldd": "sample string 4",
"fielde": 5
},
{
"fielda": 1,
"fieldb": "sample string 2",
"fieldc": 3,
"fieldd": "sample string 4",
"fielde": 5
},
]
FOR application/xml, text/xml
Sample:

<ArrayOfAPIname xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://url.org/API.Model">
<APIname>
<fielda>1</fielda>
<fielde>5</fielde>
<fieldc>3</fieldc>
<fieldd>sample string 4</fieldd>
<fieldb>sample string 2</fieldb>
</APIname>
<APIname>
<fielda>1</fielda>
<fielde>5</fielde>
<fieldc>3</fieldc>
<fieldd>sample string 4</fieldd>
<fieldb>sample string 2</fieldb>
</APIname>
</ArrayOAPIname>

[SnowInCo]

Please ignore my last post if you will.
I would greatly appreciate any assistance you can give me in the following revised attempt.
I'm getting an error message where it is commented out.

HERE IS WHAT I'M TRYING:

$url = "apiurl";
$json = file_get_contents($url);
$data = json_decode($json);

var_dump($data); // 

$json = file_get_contents('APIURLHERE');
$data = json_decode($json,true);

$field1 = $data['field1'];

echo "<pre>";

print_r($field1);

$values=array();
$i = 0;
//errormessage is on the next line
foreach($field1 as $field1value){
    $line="(";
    foreach($field1value as $key => $value){
        $line = $line. "'". $value . "',";
    }
    $line = substr($line, 0, strlen($line)-1).")";
    $values[$i] = $line;
    ++$i;
}
$values = implode(",", $values);
echo $values;

$username = "uname";
$password = "pwd";
$hostname = "host"; 

$dbhandle = mysql_connect($hostname, $username, $password)
or die("Unable to connect to MySQL");

$selected = mysql_select_db("xxx",$dbhandle)
or die("Could not select xxx");



// Insert $event array into table

$db_insert = mysql_query("INSERT INTO mysqltablename (field1, field2, dateField, month, address, address2, city, state, zip, county, phone, fax, URL) VALUES" . $values);

if (!$db_insert)
{
    die('Could not connect - event insert failed: ' . mysql_error());
}

HERE IS WHAT I'M GETTING:

array(1) { [0]=> object(stdClass)#1 (13) { ["field1"]=> int(7) ["field2"]=> string(22) "Valueforfield2." [AdateField"]=> string(19) "1861-08-02T00:00:00" ["month"]=> string(7) "January" ["address"]=> string(16) "1212 address drive" ["address2"]=> string(0) "" ["City"]=> string(3) "city" ["State"]=> string(2) "state" ["zip"]=> string(10) "numerical zip" ["county"]=> string(0) "" ["phone"]=> string(12) "111-111-1111" ["fax"]=> string(12) "222-222-2222" ["URL"]=> string(0) "" } }

Warning:  Invalid argument supplied for foreach() in /php/phpaddress.php on line 568

Could not connect - event insert failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

I know I butchered the array… I simply don't understand it…
Thanks in advance for your guidance.