How do I access a Mysql resource from another PHP file? 1

[zombee]

This probably has to do with the issue of scope or global variables,
or perhaps it's an issue of a pointer to a resource.

I have this Mysql result set I queried, for which I need to fetch
a row one by one. The fetch, however, is done from another PHP file,
one that javascript calls via ajax.

Firstly, how can I fetch from the original queried resource from another php file?
And, if I have to delete a prior record from the table originally queried,
do I lose my position with regard to this original resource?

 

[DonQuichote]

In short: you can't. HTTP is stateless. Before your javascript runs, the result set is gone. You can store the results in a session, however, or use it to built javascript source. Using server calls in a loop is not exactly optimal AJAX programming...

+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)

 

[YoungManRiver]

zombee,

I assume you are talking about getting the PHP processing to actually queue on a click and then via either GET or POST call you MySQL processing file?

If you are here is some code that may solve your problem:

First the basics!  You must have:
<FORM METHOD="POST" NAME="form" ACTION=$_SERVER['PHP_SELF']>
</FORM>
or some variation of the "form" tag in your code for PHP to process.

Next you need:

For a button:
<input type=submit name=prc value="Submit" onclick="document.forms['form'].submit();">
or
<input type=submit name=prc value="Submit" onclick="value=prc">
I suggest the later.
For option field:
<SELECT NAME="inv_cmp" onchange="document.forms[\'form\'].submit();">'.$list.
</SELECT>

Assuming your options list is DB gen'd to fill in the $list variable.

Then you need this test in your code:

   foreach($HTTP_POST_VARS as $key => $val) {
      echo "K=> $key V=> $val <br>";
   }

This will let you set when you select/set the vars. If you use the same "name" and then use the "onclick=name" where javascripting sets the name for you, then in the loop your test will be:

if ($key == $val) {
   some process
}

which will let you process your form and interact with MySQL.

Hope this is what you are looking for.

YMR

 

[AdaHacker]

It depends: do you want to store the query result resource for access by another script, or the data returned by that query? If all you want is the data, you can fetch all the rows and save them in a session variable, e.g.

$results = mysql_query($somequery);
$_SESSION['results'] = array();
while ($row = mysql_fetch_assoc($result)) {
   $_SESSION['results'][] = $row;
}

You could then loop through the contents of $_SESSION['results'] in your other file.

If, on the other hand, you want to store the result set itself, i.e. the value returned by mysql_query(), then that's impossible. The result set is a resource and, as such, is not serializable. That means it can't be stored in a session, converted to a string to put in a POST, or anything like that. If you really need the query result set, then you just need to run the query again.

 

[YoungManRiver]

zombee,

Did we collectively answer your question or are you still needing help on this?

YMR

 

[zombee]

The scenario is like this:
The 1st php file allows the user to query a table based
on some criteria, eg "Let's see who I haven't called in the last 3 weeks."
He then inspects the 1st 20 records, as well the count of the entire resultset.
If that's the resultset he wants to now process, he hits a button to "Begin Session".
This takes him to a communication screen.

One of the buttons in the communication screen reads "Next Prospect" - at which time
the next prospect's record is fetched for communication purposes.
DonQuichote says that's not a good ajax paradigm.
Probably he thought I'm processing each quickly, one after another - which I'm not.
YoungManRiver - I'm not "submitting" on every click. When it's time to process
the next Prospect, I hit the Next Prospect button that makes an ajax call to retrieve the next record of the resultset.

Pardon my ignorance, but can I not use a reference to the resultset in the
querying php file, and refer back to it from the communication screen that way?

 

[vacunita]

Refer to my answer in the mysql thread here:

thread436-1476790

To sum up, once you leave a PHP page whatever happened there is wiped out and is no longer available.

With this new bit of info my include scenario would not be what you are looking for.

Again either you re-run your query, or you sotre all the data from the result set somewhere like a session var and use it in your next page.

Otherwise you cannot reference a result set generated in a previous page its just not the way it works.





----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[jpadie]

assumedly you are using a limit clause for your first query. in which case you would not want to reuse that resource anyway.

secondly assumedly you want to have the resource available to the second script for performance reasons. my response would be that retrieving an entire resultset when you want only the first 20 rows will probably end up being a bigger performance drain than anything else.

saving a large resultset as an array in a session variable would not be performance friendly.

the best option is to rerun the query without the limit clause

you can still do all of this as one single script of course. that would be my preferred method.

 

[AdaHacker]

Forget about passing the result set between requests. It just isn't possible. And you don't need to do it anyway. For the scenario described above, re-querying the database is just as easy.

What you're describing is basic record paging. The standard method for this is to re-run your query on every request, with a "LIMIT $page_size OFFSET $page_offset" in your SQL. That will give you a result set containint $page_size records starting $page_offset records in from the beginning of the data set. You would keep track of your query parameters and the total row count of the data set in $_SESSION. The offset/page number would usually be in the query string or POST data, e.g. /script.php?page=3. You can figure out when you're out of data by comparing the row count and your current offset (of just when the query returns no rows).

Hopefully that gives you the basic idea. All you really need to do is keep a session variable (or something equivalent) that keeps track of where you are in the data set and use an OFFSET clause in your query to jump right to that record. It isn't really that difficult.

 

[YoungManRiver]

zombee,

Also what you are describing is simple with MySQL view on the back end, so you can write your PHP code to create the view in MySQL, using the mysql_query($sql) command where $sql is the "CREATE VIEW whatever" command and then these 20 rows of data are always there, until you return to the first sreen, select the next set and destroy and recreate the next view.

Then you do not have to worry about the offset that AdaHacker described because the view contains exactly what you told it to contain using the starting row index and the limit.

Views will keep your DB running efficiently, and this is good especially if you have lot's of people accessing your app.

YMR

 

[AdaHacker]

...so you can write your PHP code to create the view in MySQL...

Interesting thought, but I don't think that's such a good idea. If you're talking about creating a view in order to preserve the search parameters, that's serious overkill. If you're creating and destroying the same view name, it also introduces concurrency issues. What happens if Bob starts a search and then, while he's going through the records, Fred starts one? Answer: Fred's search will clobber Bob's and Bob will start seeing the wrong data. Of course, you could get around that by creating per-user views, but then you end up with a bazillion views in your database.

Using views also doesn't address the issue of record paging, which was zombee's original problem. Sure, you could create the view including the offset, but you still have to know what offset to put into it. That means you have to track it yourself or parse it off the output of a SHOW CREATE VIEW query. And, incidentally, this would not only worsen the concurrency issues mentioned abouve, but would double your database load, as you're now running 2 queries per request instead of one - one to recreate the view and another to get the data.

So yes, views are good. But this isn't the kind of scenario they're meant for. In this case, trying to solve the problem with views is going to cause way more problems than it solves.