Gossamer Forum
Home : Products : DBMan : Customization :

auto add record

Quote Reply
auto add record
Hi,
Has this been done before if so please point me in the right direction?

I have a form search html page that when a number is input, from a single input box it returns the results of the request as a html page (see the code returned below).

I have two questions
A how do I extract the information from the page example name, request, ID, Make, Model etc without the html.
Two, how would I get it into a form so it could be further edited or automated into DBman
In essences I would like to extract the data form the returned html below and put it into DBman.
Has automated add to DBMAN been discussed as I have not been able to find anything?

Any help here would be a appreciated.
Thanks for your time
Norvin

################ CODE RETURNED FROM SEARCH
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>aaa</title>
<style type="text/css">
<!--
html,body {
margin: 0;
padding: 0;
height: 100%;
margin-left: 0px;
margin-top: 0px;
margin-right: 0px;
margin-bottom: 0px;
}
.normtext {
font-family: Verdana, sans-serif;
font-size: 12px;
font-weight: bold;
color: #000000;
margin:0;
}
.normwhite {
font-family: Verdana, sans-serif;
font-size: 12px;
font-weight: bold;
color: #ffffff;
margin:0;
}
.input {
font-family: Verdana, sans-serif;
font-size:12px;
font-weight:bold;
color:#3e3f76;
background-color:#EFEFEF;
border:1px solid #C3C0B2;
}
-->
</style>
</head>

<body>

<br><br>
<table width='90%' border='0' align='center' cellpadding='1' cellspacing='0'>
<tr>

<td bgcolor='#000000'>
<table width='100%' border='0' cellpadding='1' cellspacing='0'>
<tr bgcolor='#000000'>
<td class='normwhite'>Ref Number</td>
<td class='normwhite'>Name</td>
<td class='normwhite'>Telephone</td>
<td class='normwhite'>E-Mail</td>

<td class='normwhite'>Make</td>
<td class='normwhite'>Model</td>
</tr>
<tr bgcolor='#FFFFFF'>
<td class='normtext'>OG464675</td>
<td class='normtext'>N SIMONDO</td>
<td class='normtext'>01709 378675</td>

<td class='normtext'>fred@i-have.info</td>
<td class='normtext'>1986 AUDI</td>
<td class='normtext'>100</td>
</tr>
</table>
</td>
</tr>

</table>
<br>
<table width='90%' border='0' align='center' cellpadding='1' cellspacing='0'>
<tr>
<td bgcolor='#000000'>
<table width='100%' border='0' cellpadding='1' cellspacing='0'>
<tr bgcolor='#000000'>
<td class='normwhite'>Date</td>

<td class='normwhite'>VRM</td>
<td class='normwhite'>Body Style</td>
<td class='normwhite'>Engine</td>
<td class='normwhite'>Fuel</td>
<td class='normwhite'>Trans</td>
<td class='normwhite'>Location</td>

</tr>
<tr bgcolor='#FFFFFF'>
<td class='normtext'>2007-01-15 13:34:20</td>
<td class='normtext'></td>
<td class='normtext'>4 DOOR</td>
<td class='normtext'>2300</td>
<td class='normtext'>PETROL</td>

<td class='normtext'>5 SPEED MANUAL</td>
<td class='normtext'>NORTH</td>
</tr>
</table>
</td>
</tr>
</table>
<br>

<table width='90%' border='0' align='center' cellpadding='1' cellspacing='0'>
<tr>
<td bgcolor='#000000'>
<table width='100%' border='0' cellpadding='1' cellspacing='0'>
<tr bgcolor='#000000'>
<td width='20%' class='normwhite'>Part</td>
<td class='normwhite'>Description</td>
</tr>

<tr bgcolor='#FFFFFF'>
<td class='normtext'>BONNET</td>
<td class='normtext'>Bonnet</td>
</tr>
</table>
</td>
</tr>
</table>

<br><br>
<form action="refsearch.php" method="POST">
<table width="300" border="0" align="center" cellpadding="1" cellspacing="0">
<tr>
<td bgcolor="#000000">
<table width="300" border="0" cellpadding="0" cellspacing="0" align="center">
<tr align="center" bgcolor="#000000">
<td height="18" colspan="2"><span class="normwhite">Search By Reference Number </span></td>
</tr>

<tr bgcolor="#FFFFFF">
<td height="25" align="right" class="normtext">Reference:&nbsp;</td>
<td valign="middle"><input name="reference" type="text" size="15" class="input">
<input name="Search" type="submit" class="input" value="Search"></td>
</tr>
</table>
</td>
</tr>

</table>
</form>


</body>
</html>
UnsureUnsure
Quote Reply
Re: [Syte] auto add record In reply to
Quote:
it returns the results of the request


From where? Where are the results stored? It's a lot easier to pull results from where they are stored than from an html page.


JPD
----------------------------------------------------
JPDeni's DBMan-ual
How to ask questions the smart way.
Quote Reply
Re: [JPDeni] auto add record In reply to
Hi,
Thanks for the reply,

The results are stored on another server presumably in a sql database,

They have asked me how I want the results sent once I have typed in the code number into a form called from their site, so they wrote a small script that returned the results as an html file.
I dont feel that they would want others to access their database.
What other option do I have to get the data from a form, once a number is typed in, from their database, how could they return the results to my server?

What would be the best way to get the results into DBMAN

Thanks JPD good to hear from you again, and how are the cats?
You helped me out with some coding years back.
Cheers

Regards

Norvin

The Form
<form action="refsearch.php" method="POST">
<table width="300" border="0" align="center" cellpadding="1" cellspacing="0">
<tr>

<td bgcolor="#000000">
<table width="300" border="0" cellpadding="0" cellspacing="0" align="center">
<tr align="center" bgcolor="#000000">
<td height="18" colspan="2"><span class="normwhite">Search By Reference Number </span></td>
</tr>
<tr bgcolor="#FFFFFF">
<td height="25" align="right" class="normtext">Reference:&nbsp;</td>
<td valign="middle"><input name="reference" type="text" size="15" class="input">

<input name="Search" type="submit" class="input" value="Search"></td>
</tr>
</table>
</td>
</tr>
</table>
</form>
Quote Reply
Re: [Syte] auto add record In reply to
I see. Well, it might be best if you had it sent in a plain text file, in the format of your .db file. That would be the ideal because it could just be appended to your .db file.

If not, we can probably figure out a way to strip out all of the html and come up with a plain file with just the data. It would require a separate .cgi script to process the data, which is what I guess you were asking for to start with. :)

The cats are great. I'll tell them that you asked about them. LOL. They're both burrowed under the blankets on the bed now, I think.


JPD
----------------------------------------------------
JPDeni's DBMan-ual
How to ask questions the smart way.
Quote Reply
Re: [JPDeni] auto add record In reply to
Hi,
Thanks I have sent a request for them to send the results back as post or as a plain text file so as you say, it the data could be extracted.

The other question how to automate the adding of records to DBMAN
Example results are generated from a cgi that need adding automatically to DBMAN as a record.
Thanks again, for your time.
Regards

Norvin
Quote Reply
Re: [Syte] auto add record In reply to
Once I see how the data is structured, I can better figure out how to add it to DBMan. I'm going to have to let this work around in my brain a little bit, though. Often if I give an answer right away, something better comes along after I think about it a bit, so it's better to take a little time to think about it before giving an answer.


JPD
----------------------------------------------------
JPDeni's DBMan-ual
How to ask questions the smart way.
Quote Reply
Re: [JPDeni] auto add record In reply to
Hi,
Re the file above.
I have managed to get the return to write a file on the server in a | delimited format see below.


############################File
MP113126||JOE|BLOGGS|01709 345676|joe@sanipass.com|1986|AUDI|100|2007-01-27 01:19:07|4 DOOR|2300|PETROL|5 SPEED MANUAL|NORTH|ABS MODULATOR|Abs Modulator left side
#########################File end



How would I now get that file contents automatically added to an existing DBMAN database?
Thanks for any time given
Regards
Norvin
Quote Reply
Re: [Syte] auto add record In reply to
Cool!

So the user inputs something into a form, a file sends the input to an SQL database, does the search and returns the results of the search in a file which is then saved on your server. Right?

What else does that program do? The form is likely on an html page and there is a line that says

Code:

<form action="....


After the action tag, there is a reference to the file that sends the input to the SQL database. It has to do something else, too, though, if only to print out "Thanks for using the search." Is there any way I can know what the content of that program is?


JPD
----------------------------------------------------
JPDeni's DBMan-ual
How to ask questions the smart way.
Quote Reply
Re: [JPDeni] auto add record In reply to
Hi,
Thanks for taking the time,
Yes the php contains a form for a item number mQ23456 it the writes a file to the directory it is in and produces the results on screen thml format.

However I was thinking of changing it to Perl cgi as I cant get it to execute on the unix server I need it on but works fine on the old Raq4.
Example
http://www.i-have.info/on/refsearch.php type MP113126
and the results are written to a file.
I need now to get it into DBMAN that contains many fields, as well as the ones that are returned by the php request.

Thanks for your time JPD

Kind regards

Norvin
########################################## PHP start ##############
<?
//##############################
//MYSQL DATABASE VARIABLES
$user1 = "XXXXXgat";
$pass1 = "XXXXX93";
$db = "spares";
$server = "www.joe.co.uk";

//SETUP MYSQL CONNECTION
$link = mysql_connect ($server, $user1, $pass1);
if (!$link) { die ("Couldn't connect to mySQL server"); }
if (!mysql_select_db ($db, $link) ) { die ("Couldn't open $db: ".mysql_error() ); }
//##############################
?>

<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Search 247Spares</title>
<style type="text/css">
<!--
html,body {
margin: 0;
padding: 0;
height: 100%;
margin-left: 0px;
margin-top: 0px;
margin-right: 0px;
margin-bottom: 0px;
}
.normtext {
font-family: Verdana, sans-serif;
font-size: 12px;
font-weight: bold;
color: #000000;
margin:0;
}
.normwhite {
font-family: Verdana, sans-serif;
font-size: 12px;
font-weight: bold;
color: #ffffff;
margin:0;
}
.input {
font-family: Verdana, sans-serif;
font-size:12px;
font-weight:bold;
color:#3e3f76;
background-color:#EFEFEF;
border:1px solid #C3C0B2;
}
-->
</style>
</head>

<body>

<?
//##############################
if($Search) {
$query = "Select * from 247_partfinder_texts where Reference='$reference'";
$result = mysql_query($query);
$numrows = mysql_num_rows($result);

if($numrows != "0") {
$row = mysql_fetch_array($result);
//##############################
if ($row[Fuel] == 'p') { $row[Fuel] = 'PETROL'; }
if ($row[Fuel] == 'd') { $row[Fuel] = 'DIESEL'; }
if ($row[Fuel] == 'pt') { $row[Fuel] = 'TURBO PETROL'; }
if ($row[Fuel] == 'dt') { $row[Fuel] = 'TURBO DIESEL'; }
if ($row[Fuel] == 'cdi') { $row[Fuel] = 'DIESEL CDi'; }
if ($row[Fuel] == 'dci') { $row[Fuel] = 'DIESEL DCi'; }
if ($row[Fuel] == 'dti') { $row[Fuel] = 'DIESEL DTi'; }
if ($row[Fuel] == 'lpg') { $row[Fuel] = 'LPG'; }
if ($row[Fuel] == 'tdi') { $row[Fuel] = 'DIESEL TDi'; }

if ($row[Trans] == '5m') { $row[Trans] = '5 SPEED MANUAL'; }
if ($row[Trans] == '4m') { $row[Trans] = '4 SPEED MANUAL'; }
if ($row[Trans] == '6m') { $row[Trans] = '6 SPEED MANUAL'; }
if ($row[Trans] == '3a') { $row[Trans] = '3 SPEED AUTOMATIC'; }
if ($row[Trans] == '4a') { $row[Trans] = '4 SPEED AUTOMATIC'; }
if ($row[Trans] == '5a') { $row[Trans] = '5 SPEED AUTOMATIC'; }
if ($row[Trans] == '4x4m') { $row[Trans] = '4x4 MANUAL'; }
if ($row[Trans] == '4x4a') { $row[Trans] = '4x4 AUTOMATIC'; }
if ($row[Trans] == 'trip') { $row[Trans] = 'TRIPTRONIC'; }
if ($row[Trans] == 'step') { $row[Trans] = 'STEPTRONIC'; }
if ($row[Trans] == 'switc') { $row[Trans] = 'SWITCHABLE AUTOMATIC'; }
//##############################
echo "<br><br>
<table width='90%' border='0' align='center' cellpadding='1' cellspacing='0'>
<tr>
<td bgcolor='#000000'>
<table width='100%' border='0' cellpadding='1' cellspacing='0'>
<tr bgcolor='#000000'>
<td class='normwhite'>Ref Number</td>
<td class='normwhite'>Name</td>
<td class='normwhite'>Telephone</td>
<td class='normwhite'>E-Mail</td>
<td class='normwhite'>Make</td>
<td class='normwhite'>Model</td>
</tr>
<tr bgcolor='#FFFFFF'>
<td class='normtext'>$row[Reference]</td>
<td class='normtext'>$row[Name] $row[LastName]</td>
<td class='normtext'>$row[Telephone]</td>
<td class='normtext'>$row|$row[Year]|$row[Make]|$row[Model]|$row[DateofPost]|$row[BodyStyle]|$row[EngineSize]|$row[Fuel]|$row[Trans]|$row[Location]|$row[Part]|$row[Description]";
//########################################
$filename = "$row[Reference].txt";

//###Open File############################
if (!$handle = fopen($filename, 'w')) {
echo "Cannot open file ($filename)";
exit;
}
// Write $somecontent to our opened file.
if (fwrite($handle, $reqstring) == FALSE) {
echo "Cannot write to file ($filename)";
exit;
}

echo "<br><center><span class='normtext'>Success! Wrote to file ($filename)</span></center><br>";
fclose($handle);
//########################################

}
else { echo "<br><center><b><font color='#990000'>Sorry, Requested Reference Number Not Found!</font></b></center>"; }

}
?>
<br><br>
<form action="refsearch.php" method="POST">
<table width="300" border="0" align="center" cellpadding="1" cellspacing="0">
<tr>
<td bgcolor="#000000">
<table width="300" border="0" cellpadding="0" cellspacing="0" align="center">
<tr align="center" bgcolor="#000000">
<td height="18" colspan="2"><span class="normwhite">Search By Reference Number </span></td>
</tr>
<tr bgcolor="#FFFFFF">
<td height="25" align="right" class="normtext">Reference:&nbsp;</td>
<td valign="middle"><input name="reference" type="text" size="15" class="input">
<input name="Search" type="submit" class="input" value="Search"></td>
</tr>
</table>
</td>
</tr>
</table>
</form>

</body>
</html>
########################################### END
Quote Reply
Re: [Syte] auto add record In reply to
This is off the top of my head, so it would have to be further refined, but I'm thinking we could add another step to the Add function, incorporating your php file into DBMan, so the process would be something like...

--Click Add link

--DBMan displays "search for reference number form" (like the one you have now) [this would require another subroutine in html.pl]

--There is the SQL search of the external database [this would require another subroutine in db.cgi]

--Assuming the search is returned*, the DBMan displays its regular Add form, with the values from the SQL database entered

--You fill in the other field values and add the record as usual

* If there was nothing returned from the SQL search, an error page would be displayed.

In theory. :)

I've never accessed SQL via Perl. I'm sure I can figure out how to do it, though.


JPD
----------------------------------------------------
JPDeni's DBMan-ual
How to ask questions the smart way.
Quote Reply
Re: [JPDeni] auto add record In reply to
Hi,
That is correct you have it in one,
The line

--Assuming the search is returned*, the DBMan displays its regular Add form, with the values from the SQL database entered

However the "search is returned*, Would also display some other input fields from the database, extra description etc.

But your description is correct.

Kind Regards
Norvin

Last edited by:

Syte: Jan 30, 2007, 11:07 AM
Quote Reply
Re: [Syte] auto add record In reply to
Right. That was why I included

--You fill in the other field values and add the record as usual

as the next step.

I'll just have to figure out how to do all those SQL functions through Perl.

None of this will happen until next week. I've got quite a bit on my plate this week and I'm going out of town for the weekend.


JPD
----------------------------------------------------
JPDeni's DBMan-ual
How to ask questions the smart way.
Quote Reply
Re: [JPDeni] auto add record In reply to
Hi,
Very much appreciated and I can imagine that you more than burn the midnight oil working.
Thank you very much for you support

Kind Regards

Norvin
Smile
Quote Reply
Re: [Syte] auto add record In reply to
Back from my trip. :)

I need to know the names of the fields in your database, especially the names of the fields that will be populated from the SQL database.


JPD
----------------------------------------------------
JPDeni's DBMan-ual
How to ask questions the smart way.
Quote Reply
Re: [JPDeni] auto add record In reply to
Hi
Thanks for getting back to me.
The fields I will be using are below from the flat file version though I will be running the database on the Sql version with the same fields.
the fields that will come fom the other database are
Part_ref_no
Name
Tele
email
make
model
date
body
Engine fuel
Transmission
Location
All of them are in the database field list
Thanks for your support, much appreciated.

Regards

Norvin
Main Database Field List;
%db_def = (

ID => [0, 'numer', 5, 8, 1, '', ''],
Title => [1, 'alpha', 40, 255, 1, '', ''],
URL => [2, 'alpha', 40, 255, 1, 'http://', '^http://'],
Date => [3, 'date', 12, 15, 1, &get_date, ''],
Make => [4, 'alpha', 0, 60, 1, '', ''],
Model => [5, 'alpha', 0, 3, 0, '', ''],
Reg => [6, 'alpha', 0, 2, 1, '', ''],
Year => [7, 'alpha', 0, 2, 1, '', ''],
Engine => [8, 'alpha', 0, 40, 0, '', ''],
Fuel => [9, 'alpha', 0, 3, 1, 'Petrol', 'Petrol|Diesel'],
Transmission => [10, 'alpha', 0, 3, 0, '', ''],
Body => [11, 'alpha', 0, 3, 0, '', ''],
'Part Description' => [12, 'alpha', 0, 3, 0, '', ''],
Part_ref_no => [13, 'alpha', 0, 3, 0, '', ''],
Part => [14, 'alpha', 0, 3, 0, '', ''],
Description => [15, 'alpha', 0, 3, 0, '', ''],
Name => [16, 'alpha', 0, 3, 0, '', ''],
sname => [17, 'alpha', 0, 3, 0, '', ''],
Address => [18, 'alpha', 0, 3, 0, '', ''],
Address2 => [19, 'alpha', 0, 3, 0, '', ''],
Town => [20, 'alpha', 0, 3, 0, '', ''],
'County/State' => [21, 'alpha', 0, 3, 0, '', ''],
Pcode => [22, 'alpha', 0, 3, 0, '', ''],
e_mail => [23, 'alpha', 0, 3, 0, '', ''],
Country => [24, 'alpha', 0, 3, 0, '', ''],
Postage => [25, 'alpha', 0, 3, 0, '', ''],
'Card Type' => [26, 'alpha', 0, 3, 0, '', ''],
'Card Number' => [27, 'alpha', 0, 3, 0, '', ''],
'Switch No' => [28, 'alpha', 0, 3, 0, '', ''],
'Name on Card' => [29, 'alpha', 0, 3, 0, '', ''],
'Expiry Date' => [30, 'alpha', 0, 3, 0, '', ''],
Code => [31, 'alpha', 0, 3, 0, '', ''],
shippingname => [32, 'alpha', 0, 3, 0, '', ''],
shippingaddress => [33, 'alpha', 0, 3, 0, '', ''],
shippingaddress2 => [34, 'alpha', 0, 3, 0, '', ''],
shippingtown => [35, 'alpha', 0, 3, 0, '', ''],
shippingcounty => [36, 'alpha', 0, 3, 0, '', ''],
shippingpcode => [37, 'alpha', 0, 3, 0, '', ''],
Terms => [38, 'alpha', 0, 3, 0, '', ''],
Referer => [39, 'alpha', 0, 3, 0, '', ''],
'Notes' => [40, 'alpha', 0, 3, 0, '', ''],
extra1 => [41, 'alpha', 0, 3, 0, '', ''],
location => [42, 'alpha', 0, 3, 0, '', ''],
Userid => [43, 'alpha', -2, 15, 0, '', '']

);
Quote Reply
Re: [Syte] auto add record In reply to
A couple of questions....

First, are you using the default version of the html.pl file or one of my alternate versions?

Second, I just reread a sentence in your last post and I need a little explanation:
Quote:

The fields I will be using are below from the flat file version though I will be running the database on the Sql version with the same fields.

Are you going to be using DBMan SQL? What I'm writing is for the flat file version of DBMan and will be completely useless for the SQL version. I want to be sure that you understand that. I don't have anything to do with DBMan SQL.


JPD
----------------------------------------------------
JPDeni's DBMan-ual
How to ask questions the smart way.
Quote Reply
Re: [JPDeni] auto add record In reply to
Hi
Cheers for that and thanks for your time,

The flat file version is fine.
As to the html.pl I will initially be starting with the default file version however I will be modifying it later for display purposes, and to integrate some other shopping cart variables.

Thanks again.

Norvin
Quote Reply
Re: [Syte] auto add record In reply to
Hi,

Was the info I sent OK, I will stick with the flat file version as it is easier for me to intergate.
Thanks again for your time

Kind Reagrds

Norvin
Quote Reply
Re: [Syte] auto add record In reply to
I'm sorry. I have not been feeling well at all and programming requires a clear head.


JPD
----------------------------------------------------
JPDeni's DBMan-ual
How to ask questions the smart way.
Quote Reply
Re: [Syte] auto add record In reply to
It's really hard for me to say that I can't do something. But I can't do this. If you need this programming, you're going to need to pay a professional Perl programmer who has more knowledge and experience than I have.

Sorry.


JPD
----------------------------------------------------
JPDeni's DBMan-ual
How to ask questions the smart way.
Quote Reply
Re: [JPDeni] auto add record In reply to
Norvin, see if this post helps you any (scroll all the way down to the bottom).

http://www.gossamer-threads.com/perl/gforum/gforum.cgi?do=post_attachment;postatt_id=1968;

It is set up to modify a plain version of dbman to allow a text-file to be imported into the "add record" screen.

The biggest question is where does the "exported" SQL generated file live? On their server? If you can adapt this mod to import your file (in the format you want) it may work for you.

Good Luck!
Quote Reply
Re: [Watts] auto add record In reply to
Hi ,

Thanks for the time,

However has no one ever done this before,

I have a file on the server so

ID|Name|address|Part|etc

And would like to append it to my existing database in DBMAN also pipe delimited.

ID|Name|address|Part|etc

If anyone could point me in the right direction to do this or as JPD says I would be happy to pay someone to do the script for me.

Anyone have any ideas

Cheers for the time

Basically I wanting to add to the database file created by DBNAM


Norvin
Quote Reply
Re: [Watts] auto add record In reply to
Cheers,

I will have a look at the mod and see what i can do.

Thanks for the time

Norvin